Hàm VLOOKUP trong Excel: Cách sử dụng từ đơn giản tới nâng cao

Cùng Phòng GD&DT Sa Thầy đọc bài viết Hàm VLOOKUP trong Excel: Cách sử dụng từ đơn giản tới nâng cao.

Vlookup là hàm tra cứu dữ liệu, giá trị cực hữu ích trong Microsoft Excel. Nếu bạn chưa biết rõ về nó, hãy cùng VietJack 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…

Bạn đang xem: Hàm VLOOKUP trong Excel: Cách sử dụng từ đơn giản tới nâng cao

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 VietJack để 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ị:

• Lookup_value: 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…
• Table_array: Bảng giá trị dò tìm hay dải ô chứa giá trị tra cứu, hãy nhớ giá trị tra cứu (Lookup_value) 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é.
• Col_index_num: 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 sang đến cột cần lấy dữ liệu xem là cột thứ mấy.
• Range_lookup: Phạm vi tìm kiếm, bạn có thể để 1 hoặc True tương đương với trả về dữ liệu tương đối (áp dụng rất tuyệt trong việc trả về dữ liệu trong khoảng từ bao nhiêu tới bao nhiêu nha), 0 hoặc False tương đương trả về dữ liệu tuyệt đối.

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 Table_array) 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ị Col_index_num – 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(value,IF(test,table1,table2),col,match)

Để 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ố “table_array” 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, table1 sẽ trả về giá trị nếu đúng. Khi C4 lớn hơn 2, table2 sẽ trả về giá trị nếu sai.

Nói cách khác, nếu thâm niên ít hơn 2, table1 được dùng làm table_array. Nếu không, đổi lại, table2 được dùng cho table_array.

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(test,VLOOKUP (value,table1,col,match),VLOOKUP (value,table2,col,match))

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 (Table_array).

Có nhiều nguyên do dẫn tới việc này là có thể giá trị tra cứu (Lookup_value) ở 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 Table_array khiến cho dữ liệu cần tìm kiếm (Lookup_value) không nằm ở cột đầu tiên của bảng Table_array…

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 (Range_lookup = 0).

Lưu ý khác

• Range_lookup 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 range_lookup 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 range_lookup 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 range_lookup, 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 range_lookup 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 range_lookup 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, VietJack 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!

Đăng bởi: Phòng Giáo Dục và Đạo Tạo Sa Thầy

Chuyên mục: Tổng hợp