Hàm VLOOKUP: Hàm tìm kiếm trong Excel từ bảng dữ liệu cho trước

Vlookup là hàm tìm kiếm dữ liệu cực hữu ích trong Microsoft Excel. Nếu bạn chưa biết rõ về nó, hãy cùng Download.vn tìm hiểu ý nghĩa và cách dùng hàm Vlookup trong Excel nhé!

Hàm Vlookup là hàm tìm kiếm giá trị để trả về giá trị khác trong 1 bảng tính có sẵn. Hàm Vlookup là một trong những hàm sử dụng nhiều trong Excel, áp dụng vào rất nhiều việc thực tế như tính toán tiền điện dựa trên số điện, đưa ra kết quả học tập dựa trên điểm trung bình, lấy dữ liệu nơi sinh, giới tính của nhân viên dựa vào tên hay mã nhân viên...

Mặc dù tính áp dụng rất cao nhưng nhiều người lại chưa hiểu rõ bản chất của hàm này ra sao? Cú pháp như nào? Cách sử dụng hàm này như nào? Vậy mời các bạn tham khảo bài viết dưới đây của Download.vn để hiểu rõ hơn:

Cú pháp hàm Vlookup

Cú pháp:

Giải thích nhanh: VLOOKUP(Giá trị bạn muốn tra cứu, dải ô mà bạn muốn tìm giá trị, số cột trong dải ô chứa giá trị trả về, Kết quả trả về khớp Chính xác hoặc Tương đối – được biểu thị là 0/FALSE hoặc 1/TRUE)

Giải thích từng giá trị:

• giá_trị_tìm: Giá trị dùng để tra cứu, dò tìm - đây nên là một dữ liệu không bị trùng lặp thì khả năng tìm kiếm mới chính xác. Chẳng hạn ô: Mã nhân viên, Mã học sinh, Mã hàng hoá, Mã tài khoản, Mã tài sản...
• bảng_dữ_liệu_cần_lấy: Bảng dữ liệu cần lấy chính là bảng chứa giá trị dò tìm hay dải ô chứa giá trị tra cứu, hãy nhớ giá_trị_tìm luôn phải nằm trong cột đầu tiên của bảng này. Vùng dữ liệu tìm kiếm nên để ở dạng địa chỉ tuyệt đối (có dấu $ phía trước dòng và cột bằng cách nhấn F4) để khi sao chép thì công thức vẫn chạy đúng nhé.
• thứ_tự_cột_cần_lấy_dữ_liệu: Đây là thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò tìm. Đếm từ bên trái của vùng bảng_dữ_liệu_cần_lấy sang đến cột cần lấy dữ liệu xem là cột thứ mấy.
• kiểu_lấy_dữ_liệu: Đây chính là phạm vi tìm kiếm, bạn có thể để muốn tìm kiếm tương đối (dùng biến 1 hoặc True), hoặc tìm kiếm tuyệt đối (dùng biến 0 hoặc False). Với kiểu tìm kiếm tương đối thì dữ liệu trong bảng_dữ_liệu_cần_lấy cần sắp xếp theo thứ tự tăng dần của cột đầu tiên. Kiểu tìm kiếm tuyệt đối thì không cần làm điều này.

Cách dùng hàm Vlookup trong Excel

1. Cách tìm Vlookup tương đối

Dò tìm tương đối bằng hàm Vlookup là cách tìm ra dữ liệu một cách tương đối dựa vào sự khác nhau giữa dữ liệu cột đầu tiên của vùng tìm kiếm. Để dò tìm tương đối trả về kết quả đúng thì bảng dữ liệu gốc (chính là bảng Quy định xếp loại trong ví dụ dưới đây) của bạn bắt buộc phải sắp xếp theo thứ tự tăng dần.

Ví dụ 1: Hãy xếp loại học lực cho học sinh trong danh sách dưới đây, dựa vào bảng quy định xếp loại theo điểm:

Để xếp loại học lực cho danh sách học sinh, bạn sử dụng công thức:

=VLOOKUP(C4; $C$11:$D$15; 2; 1)

Sau đó, nhấn Enter, rồi kéo xuống các ô còn lại bạn sẽ được kết quả như hình dưới:

2. Cách tìm Vlookup tuyệt đối

Ví dụ 2: Dựa vào bảng mã số nhân viên dưới đây, hãy điền thông tin về Trình độ và Quê quán của họ.

Để điền thông tin về Quê quán của nhân viên ô E23 sử dụng công thức sau:

=VLOOKUP(A23; $A$29:$C$34; 2; 0)

Và đây là kết quả sẽ thu được:

Còn điền thông tin Trình độ cho ô F23, sử dụng công thức sau:

=VLOOKUP(A23;$A$29:$C$34;3;0)

Và đây sẽ là kết quả sẽ thu được:

Cách dùng hàm Vlookup dạng nâng cao

Ngoài cách sử dụng đơn giản như trên, bạn có thể tùy biến để có 1 hàm Vlookup linh động hơn đặc biệt trong các trường hợp có bảng dữ liệu dò tìm quá rộng và việc đếm cột trở nên... mệt mỏi.

Hàm Vlookup kết hợp Match

Hàm MATCH nhằm tìm giá trị được chỉ định trong phạm vi của ô, sau đó trả về vị trí tương đối của giá trị đó trong phạm vi này. Ví dụ, nếu phạm vi A1:A3 chứa giá trị 5, 10 và 15 thì công thức =MATCH(10,A1:A3,0) sẽ trả về số 2, vì 10 là giá trị thứ hai nằm trong phạm vi A1:A3. Cú pháp hàm Match như sau:

Vậy thì áp dụng hàm Match này vào hàm Vlookup ở đoạn nào? Xin bật mí với bạn nó chính là ở giá trị thứ 3 trong hàm Vlookup: thứ_tự_cột_cần_lấy_dữ_liệu. Đưa nó vào một ví dụ cụ thể thì sẽ khiến bạn dễ hình dung hơn nhé.

Ví dụ: Cho bảng dữ liệu sau:

Cần lọc lấy dữ liệu theo tháng cho từng nhân viên ở bảng chính (cột tháng ở bảng chính có thể thay đổi). Cụ thể bảng tính sẽ như sau:

Công thức dùng trong bài:

=VLOOKUP(A3; $A$14:$G$20; MATCH($B$2;$A$13:$G$13;0); 0)

Hàm Match sẽ giúp bạn đếm số thứ tự của cột cần lấy trong bảng dữ liệu dò tìm (bảng phụ) của hàm Vlookup. Do vậy nên chỉ cần thay dữ liệu Tháng ở ô B2, bạn sẽ có dữ liệu chính xác của từng tháng tương ứng. Ví dụ dưới đây tôi thay dữ liệu cột tháng trong Bảng chính và kết quả là:

Hàm VlookUp kết hợp với hàm LEFT, RIGHT, MID

Cũng là một trong những hàm được sử dụng nhiều nhất, hàm Left/Right và Mid có thể kết hợp với Vlookup và làm nên những "kỳ tích" trong việc tìm kiếm.

Ví dụ: Cho bảng sau, hãy điền dữ liệu còn trống dựa vào các bảng phụ bên dưới:

Dữ kiện đầu vào để điền dữ liệu được mô tả như sau: 2 ký tự đầu của mã Nhân viên mô tả cho vị trí công việc, ký tự chữ ở giữa mô tả giới tính, các ký tự ở cuối mô tả cho nơi nhân viên làm việc. Dựa vào đó ta sẽ điền được công thức tính từng cột như sau:

Và đây là kết quả:

VLOOKUP với 2 bảng cần tra cứu

Công thức chung

=VLOOKUP(giá_trị_tìm;IF([điều_kiện];Bảng1;Bảng2);thứ_tự_cột;kiểu_lấy_dữ_liệu)

Để dùng hàm VLOOKUP với một mảng bảng biến, bạn có thể dùng hàm IF bên trong để kiểm soát bảng được sử dụng. Ở ví dụ dưới đây, công thức trong ô E4 là:

=VLOOKUP(D5;IF(C4<2;table1;table2);2;TRUE)

Công thức này dùng số năm thâm niên của nhân viên bán hàng để quyết định bảng tỷ lệ hoa hồng được dùng.

Hàm IF trong công thức trên được nhập như đối số bảng_dữ_liệu_cần_lấy trong VLOOKUP, tiến hành kiểm tra logic trên giá trị ở cột C “Years” - đại diện cho thâm nhiên của nhân viên. Khi C5 ít hơn 2, Bảng1 sẽ trả về giá trị nếu đúng. Khi C4 lớn hơn 2, Bảng2 sẽ trả về giá trị nếu sai.

Nói cách khác, nếu thâm niên ít hơn 2, thì dữ liệu lấy sẽ dựa vào Bảng1; còn nếu thâm niên >=2 năm thì lấy dữ liệu ở Bảng2.

Công thức thay thế

Nếu các bảng tra cứu cần những quy tắc xử lý khác nhau, bạn có thể kết hợp hai hàm VLOOKUP bên trong một hàm IF như sau:

=IF([điều_kiện];VLOOKUP(giá_trị;Bảng1;cột;kiểu);VLOOKUP(giá_trị;Bảng2;cột;kiểu))

Công thức này cho phép bạn tùy biến dữ liệu nhập vào từng VLOOKUP khi cần.

Lỗi #NA trong hàm Vlookup là gì? Cách sửa lỗi #NA

Lỗi #NA là viết tắt của từ Not Available Error có nghĩa là không tìm thấy giá trị tương ứng. Có thể trong trường hợp này bạn đã sử dụng tìm kiếm tuyệt đối và dữ liệu cần tìm không có trong dải ô chứa giá trị tra cứu (bảng_dữ_liệu_cần_lấy).

Có nhiều nguyên do dẫn tới việc này là có thể giá trị tra cứu (giá_trị_tìm) ở bảng chính và bảng phụ (trong ví dụ trên) khác nhau chỉ 1 khoảng trắng (dấu cách) thôi thì dữ liệu cũng không được tìm thấy, hoặc bạn lấy nhầm phần vùng bảng_dữ_liệu_cần_lấy khiến cho dữ liệu cần tìm kiếm (giá_trị_tìm) không nằm ở cột đầu tiên của bảng bảng_dữ_liệu_cần_lấy...

Vậy thì tùy vào từng trường hợp cụ thể mà bạn cần phải fix lỗi #NA trong hàm Vlookup cho đúng, chỉ cần nhớ 1 điều duy nhất: dữ liệu cần tìm nên đúng tuyệt đối với cột đầu tiên của bảng chứa giá trị tra cứu nếu dùng dò tìm tuyệt đối (kiểu_lấy_dữ_liệu = 0).

Lưu ý khác

• kiểu_lấy_dữ_liệu kiểm soát giá trị cần khớp chính xác hoặc không. Mặc định là TRUE = cho phép kết hợp không chính xác.
• Thiết lập kiểu_lấy_dữ_liệu sang FALSE cần một match chính xác và True để cho phép match không chính xác.
• Nếu kiểu_lấy_dữ_liệu là TRUE (cài đặt mặc định) một match không chính xác sẽ khiến hàm VLOOKUP kết hợp với giá trị gần nhất trong bảng mà có giá trị nhỏ hơn.
• Khi bỏ qua kiểu_lấy_dữ_liệu, hàm VLOOKUP sẽ cho phép sử dụng một match không chính xác nhưng nó vẫn sẽ dùng một exact match nếu có.
• Nếu kiểu_lấy_dữ_liệu là TRUE (cài đặt mặc định), đảm bảo các giá trị tra cứu ở hàng đầu tiên trong bảng được phân loại theo thứ tự tăng dần. Nếu không, VLOOKUP có thể trả về giá trị sai hay không chính xác.
• Nếu kiểu_lấy_dữ_liệu là FALSE (cần exact match), bạn không cần phân loại các giá trị ở cột đầu tiên của bảng.

Vậy là bạn đã biết cách sử dụng hàm Vlookup rồi. Trong quá trình sử dụng Excel bạn còn có thể sử dụng hàm Index để trả về giá trị trong một mảng hay hàm Max, Min cho giá trị lớn nhất và nhỏ nhất.

Khi nắm vững cú pháp, cách dùng các hàm trong Excel, bạn sẽ tự học Excel hiệu quả, nhanh chóng hơn rất nhiều. Ngoài ra, còn rất nhiều hàm hữu ích khác, Download.vn sẽ lần lượt giới thiệu tới các bạn trong những bài viết sau.

Chúc các bạn thực hiện thành công!