Hàm VLOOKUP là điểm chuẩn.
Nếu bạn biết cách sử dụng hàm Vlookup, bạn đã biết vài điều về Excel.
Nếu không, tốt hơn hết bạn không nên liệt kê Excel là một trong những điểm mạnh của mình trên sơ yếu lý lịch.
Tôi đã thực hiện một cuộc phỏng vấn nhóm và khi một ứng viên đề cập Excel là lĩnh vực chuyên môn của anh ấy, điều đầu tiên được hỏi là - bạn hiểu ý tưởng - tính năng VLOOKUP.
Bây giờ chúng ta đã biết tầm quan trọng của hàm Excel này, thật hợp lý khi nắm bắt đầy đủ nó để chúng ta có thể tự hào nói - "Tôi biết một hoặc hai điều trong Excel."
Đây sẽ là một hướng dẫn về VLOOKUP rất lớn (theo tiêu chuẩn của tôi).
Tôi sẽ trình bày tất cả về nó và sau đó chỉ cho bạn một ví dụ hữu ích và thực tế về hàm VLOOKUP.
Nội dung
- 0.1 Khi nào sử dụng hàm Vlookup trong Excel?
- 0.2 cú pháp
- 0.3 Thông số đầu vào
- 0.4 Ghi chú bổ sung (nhàm chán, nhưng quan trọng cần biết)
- 1 10 Ví dụ về hàm VLOOKUP trong Excel (Cơ bản và Nâng cao)
- 1.1 Ví dụ 1 - Tìm điểm Toán của Brad
- 1.2 Ví dụ 2 - Tra cứu hai chiều
- 1.3 Ví dụ 3 - Sử dụng menu thả xuống làm giá trị tra cứu
- 1.4 Ví dụ 4 - Tra cứu ba chiều
- 1.5 Ví dụ 5 - Nhận giá trị cuối cùng từ danh sách
- 1.6 Ví dụ 6 - Tra cứu từng phần bằng cách sử dụng ký tự đại diện và VLOOKUP
- 1.7 Ví dụ 7 - VLOOKUP trả về lỗi mặc dù khớp giá trị tra cứu
- 1.8 Ví dụ 8 - Thực hiện tra cứu phân biệt chữ hoa chữ thường
- 1.9 Ví dụ 9 - Sử dụng hàm VLOOKUP với nhiều điều kiện
- 1.10 Ví dụ 10 - Xử lý lỗi khi sử dụng hàm Vlookup
- 2 Ồ, xin chào, rất vui được làm quen với bạn.
Khi nào sử dụng hàm Vlookup trong Excel?
Hàm Vlookup hoạt động tốt nhất khi bạn đang tìm kiếm các điểm dữ liệu phù hợp trong một cột và khi tìm thấy một điểm dữ liệu phù hợp, bạn lấy cột ở bên phải hàng và lấy giá trị từ số ô đã chỉ định.
Hãy lấy một ví dụ đơn giản ở đây để hiểu khi nào sử dụng Vlookup trong Excel.
Hãy nhớ rằng, ai cũng từng điên cuồng tìm kiếm tên và điểm số của mình khi điểm thi xuất hiện và dán trên bảng thông báo (ít nhất đó là điều đã xảy ra rất nhiều khi tôi còn đi học).
Đây là cách nó hoạt động:
- Bạn vào bảng thông báo và bắt đầu tìm tên hoặc số đăng ký của mình (di chuyển ngón tay lên xuống danh sách).
- Sau khi tìm thấy tên của mình, bạn sẽ di chuyển mắt sang bên phải của tên / mã số đăng ký để xem điểm của mình.
Đó chính xác là những gì tính năng Excel VLOOKUP làm cho bạn (vui lòng sử dụng ví dụ này trong cuộc phỏng vấn tiếp theo của bạn).
Hàm VLOOKUP tìm kiếm một giá trị được chỉ định trong một cột (trong ví dụ trên, đó là tên của bạn) và khi tìm thấy một kết quả phù hợp được chỉ định, nó sẽ trả về một giá trị (mã thông báo bạn nhận được) trong cùng một hàng.
cú pháp
= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Thông số đầu vào
- lookup_value -Đây là giá trị tra cứu bạn đang cố gắng tìm ở cột ngoài cùng bên trái của bảng.Nó có thể là một giá trị, một tham chiếu ô hoặc một chuỗi văn bản.Trong ví dụ về bảng điểm, đây sẽ là tên của bạn.
- table_array -Đây là mảng bảng mà bạn đang tìm kiếm các giá trị.Đây có thể là một tham chiếu đến một phạm vi ô hoặc một phạm vi được đặt tên.Trong ví dụ về bảng điểm, đây sẽ là toàn bộ bảng chứa điểm của mọi người cho mỗi môn học
- col_index -Đây là số chỉ mục cột mà bạn muốn nhận các giá trị phù hợp.Trong ví dụ về bảng phân số, nếu bạn muốn phân số của toán học (là cột đầu tiên trong bảng chứa phân số), bạn có thể nhìn vào cột 1.Nếu bạn muốn điểm thể chất, bạn có thể nhìn vào cột 1 và 2.
- [range_lookup] -Tại đây, bạn có thể chỉ định xem bạn muốn đối sánh chính xác hay đối sánh gần đúng.Nếu bị bỏ qua, mặc định là TRUE - đối sánh gần đúng(xem thêm ghi chú bên dưới).
Ghi chú bổ sung (nhàm chán, nhưng quan trọng cần biết)
- Các kết quả phù hợp có thể chính xác (FALSE hoặc 0 trong range_lookup) hoặc gần đúng (TRUE hoặc 1).
- Khi tra cứu gần đúng, hãy đảm bảo rằng danh sách được sắp xếp theo thứ tự tăng dần (từ trên xuống dưới), nếu không kết quả có thể không chính xác.
- Khi range_lookup là TRUE (tra cứu gần đúng) và dữ liệu được sắp xếp theo thứ tự tăng dần:
- Nếu hàm VLOOKUP không thể tìm thấy giá trị, nó sẽ trả về giá trị lớn nhất nhỏ hơn lookup_value.
- Trả về lỗi # N / A nếu lookup_value nhỏ hơn giá trị tối thiểu.
- Nếu lookup_value là văn bản, các ký tự đại diện có thể được sử dụng (xem ví dụ bên dưới).
Bây giờ, hy vọng bạn đã hiểu cơ bản về những gì hàm VLOOKUP có thể làm được, hãy cùng bóc vỏ hành tây và xem một số ví dụ thực tế về hàm VLOOKUP.
10 Ví dụ về hàm VLOOKUP trong Excel (Cơ bản và Nâng cao)
Dưới đây là 10 ví dụ hữu ích về việc sử dụng Excel Vlookup để hướng dẫn bạn cách sử dụng nó trong công việc hàng ngày.
Ví dụ 1 - Tìm điểm Toán của Brad
Trong ví dụ VLOOKUP bên dưới, tôi có một danh sách với tên học sinh ở cột ngoài cùng bên trái và điểm cho các môn học khác nhau trong các cột từ B đến E.
Bây giờ chúng ta hãy làm việc và sử dụng hàm VLOOKUP để làm những gì nó hoạt động tốt nhất.Từ dữ liệu trên, tôi cần biết Brad đã đạt điểm gì trong môn toán.
Từ dữ liệu trên, tôi cần biết Brad đã đạt điểm gì trong môn toán.
Đây là công thức VLOOKUP trả về điểm toán của Brad:
= VLOOKUP ("Brad", $ A $ 3: $ E $ 10,2,0)
Công thức trên có bốn tham số:
- "Brad": - Đây là giá trị tra cứu.
- $ A $ 3: $ E $ 10 - Đây là phạm vi ô mà chúng ta đang xem xét.Hãy nhớ rằng Excel tìm kiếm các giá trị tra cứu ở cột ngoài cùng bên trái.Trong ví dụ này, nó tìm kiếm tên Brad trong A3: A10, là cột ngoài cùng bên trái của mảng được chỉ định.
- 2 - Khi hàm tìm thấy tên của Brad, nó sẽ chuyển đến cột thứ hai của mảng và trả về giá trị ở cùng hàng với Brad.Giá trị 2 ở đây có nghĩa là chúng ta đang tìm kiếm điểm từ cột thứ hai của mảng được chỉ định.
- 0 - Điều này cho biết hàm VLOOKUP chỉ tìm kiếm các kết quả phù hợp chính xác.
Đây là cách hoạt động của công thức VLOOKUP trong ví dụ trên.
Đầu tiên, nó tìm kiếm giá trị Brad ở cột ngoài cùng bên trái.Nó tra cứu giá trị trong ô A6 từ trên xuống dưới.
Khi nó tìm thấy giá trị, nó sẽ di chuyển sang bên phải trong cột thứ hai và lấy giá trị trong đó.
Bạn có thể sử dụng cùng một công thức xây dựng để lấy điểm của bất kỳ ai trong bất kỳ môn học nào.
Ví dụ: để tìm điểm của Maria trong môn Hóa học, hãy sử dụng công thức VLOOKUP sau:
= VLOOKUP ("Maria", $ A $ 3: $ E $ 10,4,0)
Trong ví dụ trên, giá trị tra cứu (tên sinh viên) được nhập trong dấu ngoặc kép.Bạn cũng có thể sử dụng tham chiếu ô có chứa giá trị tra cứu.
Lợi ích của việc sử dụng tham chiếu ô là nó làm cho công thức động.
Ví dụ: nếu bạn có một ô có tên học sinh và bạn đang nhận được điểm cho môn toán, khi bạn thay đổi tên học sinh, kết quả sẽ tự động cập nhật (như được hiển thị bên dưới):
Nếu bạn nhập giá trị tra cứu không được tìm thấy ở cột ngoài cùng bên trái, thì sẽ trả về lỗi # N / A.
Ví dụ 2 - Tra cứu hai chiều
Trong ví dụ 1 ở trên, chúng tôi đã mã hóa cứng các giá trị cột.Do đó, công thức sẽ luôn trả về điểm của môn Toán vì chúng ta sử dụng 2 làm số chỉ mục của cột.
Nhưng điều gì sẽ xảy ra nếu bạn muốn làm cho cả giá trị VLOOKUP và số chỉ mục cột là động.Ví dụ như hình bên dưới, bạn có thể thay đổi tên sinh viên hoặc tên môn học và công thức VLOOKUP sẽ được điểm chính xác.Đây là một ví dụ về công thức VLOOKUP hai chiều.
Đây là một ví dụ về hàm VLOOKUP hai chiều.
Để thực hiện công thức tra cứu hai chiều này, bạn cũng cần tạo cột động.Vì vậy, khi người dùng thay đổi chủ đề, công thức sẽ tự động chọn cột chính xác (2 cho toán học, 3 cho vật lý, v.v.).
Để làm điều này, bạn cần sử dụng hàm MATCH làm tham số cột.
Đây là công thức VLOOKUP sẽ thực hiện việc này:
=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)
Công thức trên sử dụng MATCH (H3, $ A $ 2: $ E $ 2,0) làm số cột.Hàm MATCH lấy tên chủ đề làm giá trị tra cứu (trong H3) và trả về vị trí của nó trong A2: E2.Vì vậy, nếu bạn sử dụng Math, nó sẽ trả về 2 vì Math được tìm thấy trong B2 (là ô thứ hai trong phạm vi mảng được chỉ định).
Ví dụ 3 - Sử dụng menu thả xuống làm giá trị tra cứu
Trong ví dụ trên, chúng ta phải nhập dữ liệu theo cách thủ công.Điều này có thể tốn thời gian và dễ xảy ra lỗi, đặc biệt nếu bạn có nhiều giá trị tra cứu.
Trong trường hợp này, bạn nên tạo một danh sách thả xuống các giá trị tra cứu (trong trường hợp này có thể là tên và môn học của sinh viên) và chỉ cần chọn từ danh sách.
Tùy thuộc vào lựa chọn, công thức sẽ tự động cập nhật kết quả.
Như hình bên dưới:
Đây là một thành phần bảng điều khiển tuyệt vời vì bạn có thể có một tập dữ liệu khổng lồ về hàng trăm sinh viên trong chương trình phụ trợ, nhưng người dùng cuối (giả sử là giáo viên) có thể chỉ cần thả xuống từ.
Làm thế nào để tôi làm điều này:
Công thức VLOOKUP được sử dụng trong ví dụ này giống với công thức được sử dụng trong Ví dụ 2.
=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)
Các giá trị tra cứu đã được chuyển đổi thành danh sách thả xuống.
Dưới đây là các bước để tạo danh sách thả xuống:
- Chọn ô cần danh sách thả xuống.Trong ví dụ này, trong G4, chúng ta cần tên sinh viên.
- Đi tới Dữ liệu -> Công cụ Dữ liệu -> Xác thực Dữ liệu.
- Trên tab Cài đặt của hộp thoại Xác thực Dữ liệu, hãy chọn Danh sách từ danh sách thả xuống Cho phép.
- Trong Nguồn, chọn $ A $ 3: $ A $ 10
- Bấm OK.
Bây giờ bạn sẽ thấy danh sách thả xuống trong ô G4.Tương tự như vậy, bạn có thể tạo một trong H3 cho một chủ đề.
Ví dụ 4 - Tra cứu ba chiều
Tra cứu ba chiều là gì?
Trong ví dụ 2, chúng tôi đã sử dụng một bảng tra cứu chứa điểm của các học sinh ở các môn học khác nhau.Đây là một ví dụ về tra cứu hai chiều vì chúng tôi sử dụng hai biến để lấy điểm (tên sinh viên và tên môn học).
Bây giờ, giả sử trong một năm, một học sinh sẽ tham gia ba cấp độ khác nhau của kỳ thi, bài kiểm tra đơn vị, bài kiểm tra giữa kỳ và bài kiểm tra cuối kỳ (đây là kỳ thi từ thời sinh viên của tôi).
Tra cứu ba chiều sẽ có thể nhận được điểm của học sinh cho một môn học cụ thể từ một cấp độ kỳ thi cụ thể.
Như hình bên dưới:
Trong ví dụ trên, hàm VLOOKUP có thể tra cứu và trả về điểm của các học sinh được chỉ định trong các môn học cụ thể trong ba bảng khác nhau (Bài kiểm tra đơn vị, Bài kiểm tra giữa kỳ và Điểm cuối cùng).
Đây là công thức được sử dụng trong ô H4:
=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)
Công thức này sử dụng hàm CHOOSE để đảm bảo rằng bảng chính xác được tham chiếu.Hãy phân tích phần CHỌN của công thức:
CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)
Đối số đầu tiên của công thức là IF (H2 = ”Unit Test”, 1, IF (H2 = ”Midterm”, 2,3)), kiểm tra ô H2 và xem mức độ kiểm tra được tham chiếu.Nếu đó là bài kiểm tra đơn vị, $ A $ 3: $ E $ 7 được trả về, chứa điểm kiểm tra đơn vị.Trả về $ A $ 11: $ E $ 15 nếu đó là bài kiểm tra giữa kỳ, ngược lại trả về $ A $ 19: $ E $ 23.
Làm điều này làm cho mảng bảng VLOOKUP động, làm cho nó trở thành một tra cứu ba chiều.
Ví dụ 5 - Nhận giá trị cuối cùng từ danh sách
Bạn có thể tạo công thức VLOOKUP để lấy giá trị cuối cùng trong danh sách.
Số dương lớn nhất bạn có thể sử dụng trong Excel là 9.99999999999999E + 307 . Điều này cũng có nghĩa là số tra cứu lớn nhất trong số VLOOKUP là như nhau.
Tôi không nghĩ rằng bạn sẽ cần bất kỳ phép tính nào liên quan đến những con số lớn như vậy.Đây chính xác là những gì chúng ta có thể sử dụng để lấy số cuối cùng trong danh sách.
Giả sử bạn có một tập dữ liệu như sau(trong A1: A14), và bạn muốn lấy số cuối cùng trong danh sách.
Đây là công thức bạn có thể sử dụng:
= VLOOKUP (9.99999999999999E + 307, $ A $ 1: $ A $ 14, TRUE)
Lưu ý rằng công thức trên sử dụng hàm VLOOKUP phù hợp gần đúng (Lưu ý TRUE ở cuối công thức, không FALSE hoặc 0).Ngoài ra, hãy lưu ý rằng công thức VLOOKUP này hoạt động mà không cần sắp xếp danh sách.
Đây là cách hàm VLOOKUP gần đúng hoạt động.Nó quét cột ngoài cùng bên trái từ trên xuống dưới.
- Giá trị này được trả về nếu tìm thấy kết quả khớp chính xác.
- Nếu nó tìm thấy một giá trị cao hơn giá trị tra cứu, nó sẽ trả về giá trị trong ô phía trên nó.
- Nếu giá trị tra cứu lớn hơn tất cả các giá trị trong danh sách, giá trị cuối cùng được trả về.
Trong ví dụ trên, trường hợp thứ ba là tại nơi làm việc.
do9.99999999999999E + 307là số lớn nhất có thể được sử dụng trong Excel, vì vậy khi được sử dụng làm giá trị tra cứu, nó sẽ trả về số cuối cùng trong danh sách.
Tương tự như vậy, bạn cũng có thể sử dụng nó để trả về mục văn bản cuối cùng trong danh sách.Đây là công thức có thể làm điều đó:
= VLOOKUP ("zzz", $ A $ 1: $ A $ 8,1, TRUE )
Theo cùng một logic.Excel xem xét tất cả các tên và vì zzz được coi là lớn hơn bất kỳ tên / văn bản nào bắt đầu bằng một chữ cái trước zzz, nó sẽ trả về mục cuối cùng trong danh sách.
Ví dụ 6 - Tra cứu từng phần bằng cách sử dụng ký tự đại diện và VLOOKUP
Các ký tự đại diện trong Excel rất hữu ích trong nhiều trường hợp.
Đó là lọ thuốc kỳ diệu này mang lại cho công thức của bạn siêu năng lực.
Tra cứu một phần là bắt buộc khi bạn phải tra cứu một giá trị trong danh sách và không có giá trị khớp chính xác.
Ví dụ: giả sử bạn có một tập dữ liệu như bên dưới và bạn muốn tìm công ty ABC trong danh sách, nhưng danh sách có ABC Ltd thay vì ABC.
Bạn không thể sử dụng ABC làm giá trị tra cứu vì không có kết quả khớp chính xác nào trong cột A.Các kết quả gần đúng cũng có thể dẫn đến kết quả không chính xác và danh sách cần được sắp xếp theo thứ tự tăng dần.
Tuy nhiên, bạn có thể sử dụng các ký tự đại diện trong hàm VLOOKUP để lấy các kết quả phù hợp.
Nhập công thức sau vào ô D2 và kéo công thức đó sang các ô khác:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Công thức này hoạt động như thế nào?
Trong công thức trên, thay vì sử dụng giá trị tra cứu như hiện tại, nó được bao quanh bởi dấu hoa thị ký tự đại diện (*) - "*" & C2 & "*"
Dấu hoa thị là các ký tự đại diện trong Excel có thể đại diện cho bất kỳ số ký tự nào.
Sử dụng dấu hoa thị xung quanh giá trị tra cứu để cho Excel biết rằng nó cần tìm bất kỳ văn bản nào có chứa từ trong C2.Nó có thể có bất kỳ số ký tự nào trước hoặc sau văn bản trong C2.
Ví dụ: ô C2 chứa ABC, vì vậy hàm VLOOKUP sẽ xem xét các tên trong A2: A8 và tìm kiếm ABC.Nó tìm thấy một kết quả phù hợp trong ô A2 vì nó chứa ABC từ ABC Ltd. Không quan trọng nếu có bất kỳ ký tự nào ở bên trái hoặc bên phải của ABC.Nó sẽ được coi là một trận đấu cho đến khi có ABC trong chuỗi văn bản.
Lưu ý: Hàm VLOOKUP luôn trả về giá trị khớp đầu tiên và dừng các tra cứu thêm.Vì vậy, nếu có ABC trong danh sách Ltd.và ABC Corporation, nó sẽ trả về cái đầu tiên và bỏ qua phần còn lại.
Ví dụ 7 - VLOOKUP trả về lỗi mặc dù khớp giá trị tra cứu
Nó sẽ khiến bạn phát điên khi thấy có giá trị tra cứu khớp và hàm VLOOKUP trả về lỗi.
Ví dụ, trong trường hợp bên dưới, có một kết quả khớp (Matt), nhưng hàm VLOOKUP vẫn trả về lỗi.
Bây giờ trong khi chúng ta có thể thấy rằng có một sự trùng khớp, điều mà chúng ta không thể nhìn thấy bằng mắt thường là có thể có khoảng trống ở đầu hoặc ở cuối.Nếu bạn có những khoảng trắng thừa này trước, sau hoặc giữa giá trị tra cứu, thì đó không phải là một kết quả khớp chính xác.
Điều này thường xảy ra khi bạn nhập dữ liệu từ cơ sở dữ liệu hoặc lấy dữ liệu từ người khác.Các khoảng trống ở đầu / cuối này có xu hướng chui vào.
Giải pháp ở đây là hàm TRIM.Nó loại bỏ bất kỳ khoảng trắng đầu hoặc cuối hoặc khoảng trắng thừa giữa các từ.
Đây là công thức sẽ cung cấp cho bạn kết quả chính xác.
= VLOOKUP ("Matt", TRIM ($ A $ 2: $ A $ 9), 1,0)
Vì đây là công thức mảng, hãy sử dụng Control + Shift + Enter thay vì Enter.
Một cách tiếp cận khác có thể là trước tiên xử lý mảng tra cứu của bạn bằng hàm TRIM để đảm bảo rằng tất cả các khoảng trống thừa đã biến mất, sau đó sử dụng hàm VLOOKUP như bình thường.
Ví dụ 8 - Thực hiện tra cứu phân biệt chữ hoa chữ thường
Theo mặc định, các giá trị tra cứu trong hàm Vlookup không phân biệt chữ hoa chữ thường.Ví dụ: nếu giá trị tra cứu của bạn là MATT, matt hoặc Matt, thì tất cả đều giống nhau đối với hàm VLOOKUP.Nó trả về giá trị phù hợp đầu tiên bất kể trường hợp nào.
Nhưng nếu bạn muốn tra cứu phân biệt chữ hoa chữ thường, bạn cần sử dụng hàm EXACT và hàm VLOOKUP.
Đây là một ví dụ:
Như bạn có thể thấy, ba ô có cùng tên (trong A2, A4 và A5) nhưng có cách viết hoa khác nhau.Ở bên phải, chúng tôi có ba tên (Matt, MATT và matt) và điểm của họ trong môn toán.
Hàm VLOOKUP hiện không được trang bị để xử lý các giá trị tra cứu phân biệt chữ hoa chữ thường.Trong ví dụ trên, nó luôn trả về 38, là điểm của Matt trong A2.
Để phân biệt chữ hoa chữ thường, chúng ta cần sử dụng cột trợ giúp (như hình bên dưới):
Để nhận giá trị trong cột trợ giúp, hãy sử dụng hàm = ROW ().Nó sẽ chỉ lấy số hàng trong ô.
Sau khi bạn có cột trợ giúp, đây là công thức cung cấp kết quả tra cứu phân biệt chữ hoa chữ thường.
=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)
Bây giờ chúng ta hãy chia nhỏ nó và hiểu nó làm gì:
- EXACT (E2, $ A $ 2: $ A $ 9) - Phần này so sánh giá trị tra cứu trong E2 với tất cả các giá trị trong A2: A9.Nó trả về một mảng TRUE / FALSE, trong đó TRUE được trả về trong trường hợp khớp chính xác.Trong trường hợp này, nó sẽ trả về mảng sau: {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
- EXACT (E2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9) - Phần này nhân mảng TRUE / FALSE với số hàng. Miễn là có ĐÚNG, nó sẽ cho số hàng , ngược lại Nó trả về 0. Trong trường hợp này, nó trả về {2; 0; 0; 0; 0; 0; 0; 0}.
- MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。Trong trường hợp này, nó sẽ trả về 2 (là số dòng chính xác).
- Bây giờ chúng ta chỉ sử dụng số này làm giá trị tra cứu và sử dụng mảng tra cứu như B2: C9
Lưu ý: Vì đây là công thức mảng, hãy sử dụng Control + Shift + Enter thay vì chỉ nhập.
Ví dụ 9 - Sử dụng hàm VLOOKUP với nhiều điều kiện
Hàm VLOOKUP trong Excel, ở dạng cơ bản, tìm kiếm một giá trị tra cứu và trả về giá trị tương ứng từ một hàng được chỉ định.
Nhưng thường thì hàm VLOOKUP cần thiết trong Excel với nhiều tiêu chí.
Giả sử bạn có dữ liệu chứa tên học sinh, loại bài kiểm tra và điểm toán (được hiển thị bên dưới):
Việc sử dụng hàm VLOOKUP để lấy điểm môn toán của mỗi học sinh ở cấp độ bài kiểm tra tương ứng có thể là một thách thức.
Ví dụ: nếu bạn cố gắng sử dụng hàm VLOOKUP với Matt làm giá trị tra cứu, nó sẽ luôn trả về 91, điểm cho lần xuất hiện đầu tiên của Matt trong danh sách.Để nhận được điểm Matt cho từng loại bài kiểm tra (đơn vị, kỳ thi giữa kỳ và cuối kỳ), bạn cần tạo một giá trị tra cứu duy nhất.
Điều này có thể được thực hiện bằng cách sử dụng các cột trợ giúp.Bước đầu tiên là chèn một cột trợ giúp vào bên trái của phân số.
Bây giờ, để tạo một bộ định lượng duy nhất cho mỗi trường hợp của tên, hãy sử dụng công thức sau trong C2: = A2 & ”|” & B2
Sao chép công thức này vào tất cả các ô trong cột trợ giúp.Điều này sẽ tạo ra một giá trị tra cứu duy nhất cho mỗi trường hợp của tên (như được hiển thị bên dưới):
Bây giờ, tuy có trùng lặp tên nhưng khi kết hợp tên với các cấp độ thi thì không có tên trùng lặp.
Điều này thật dễ dàng vì bây giờ bạn có thể sử dụng giá trị cột trợ giúp làm giá trị tra cứu.
Đây là công thức cung cấp cho bạn kết quả trong G3: I8.
= VLOOKUP ($ F3 & "|" & G $ 2, $ C $ 2: $ D $ 19,2,0)
Ở đây chúng tôi kết hợp tên sinh viên và cấp độ kỳ thi để lấy giá trị tra cứu, chúng tôi sử dụng giá trị tra cứu này để kiểm tra trong cột trợ giúp xem có trùng khớp với hồ sơ không.
Lưu ý: Trong ví dụ trên, chúng tôi đã sử dụng | Được sử dụng làm dấu phân cách khi thêm văn bản vào các cột phụ.Trong một số trường hợp rất hiếm (nhưng có thể xảy ra), bạn có thể có hai tiêu chí khác nhau, nhưng kết hợp chúng sẽ cho ra cùng một kết quả.Đây là một ví dụ:
Lưu ý rằng mặc dù A2 và A3 khác nhau và B2 và B3 khác nhau, nhưng sự kết hợp cuối cùng vẫn giống nhau.Tuy nhiên, nếu bạn sử dụng dấu phân cách, ngay cả sự kết hợp cũng sẽ khác nhau (D2 và D3).
Đây là hướng dẫn về cách sử dụng hàm VLOOKUP với nhiều điều kiện mà không cần sử dụng cột trợ giúp.
Ví dụ 10 - Xử lý lỗi khi sử dụng hàm Vlookup
Hàm VLOOKUP trong Excel trả về lỗi khi không thể tìm thấy giá trị tra cứu được chỉ định.Nếu hàm VLOOKUP không thể tìm thấy một giá trị, có lẽ bạn không muốn các giá trị lỗi xấu ảnh hưởng đến tính thẩm mỹ của dữ liệu của bạn.
Bạn có thể dễ dàng loại bỏ các giá trị lỗi với nội dung đầy đủ của bất kỳ ý nghĩa nào, chẳng hạn như "Không có sẵn" hoặc "Không tìm thấy".
Ví dụ, trong ví dụ dưới đây, khi bạn cố gắng tìm điểm của Brad trong danh sách, nó sẽ trả về lỗi vì tên của Brad không có trong danh sách.
Để loại bỏ lỗi này và thay thế nó bằng một cái gì đó có ý nghĩa, hãy bọc hàm VLOOKUP trong một hàm IFERROR.
Đây là công thức:
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0), "Không tìm thấy")
Hàm IFERROR kiểm tra xem giá trị được trả về bởi đối số đầu tiên (trong trường hợp này là hàm VLOOKUP) có phải là lỗi hay không.Nếu đó không phải là lỗi, hãy trả về giá trị thông qua hàm VLOOKUP, nếu không thì trả về Không tìm thấy.
Hàm IFERROR đã có từ Excel 2007.Nếu bạn đang sử dụng phiên bản trước, vui lòng sử dụng các chức năng sau:
=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))
Đó là tất cả cho hướng dẫn VLOOKUP này.
Tôi đang cố gắng trình bày ví dụ chính về việc sử dụng hàm Vlookup trong Excel.Hãy cho tôi biết trong phần nhận xét nếu bạn muốn xem thêm các ví dụ được thêm vào danh sách này.
Các hàm Excel liên quan:
- Hàm HLOOKUP trong Excel.
- Hàm XLOOKUP trong Excel
- Hàm chỉ mục trong Excel.
- Hàm gián tiếp trong Excel.
- Hàm đối sánh trong Excel.
- Hàm offset trong Excel.
