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

Vlookup là một công cụ hữu ích trong Microsoft Excel. Hãy khám phá cách sử dụng hàm Vlookup trong Excel cùng Mytour!

Hàm Vlookup giúp tìm kiếm và trả về giá trị khác trong bảng tính. Đây là một trong những công cụ phổ biến trong Excel, được áp dụng trong nhiều tình huống thực tế như tính tiền điện dựa trên số điện tiêu thụ, đưa ra kết quả học tập dựa trên điểm trung bình, lấy thông tin về nơi sinh, giới tính của nhân viên từ tên hoặc mã nhân viên...

Nhiều người chưa hiểu rõ về cách sử dụng và cú pháp của hàm này. Hãy tham khảo bài viết dưới đây từ Mytour để hiểu rõ hơn:

Cách sử dụng hàm Vlookup

Cú pháp:

Giải thích một cách nhanh gọn: 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: Đây là giá trị bạn muốn tìm kiếm - đảm bảo không bị trùng lặp để tìm kiếm chính xác. Ví dụ: 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: Đây là bảng chứa giá trị bạn muốn tra cứu, đảm bảo rằng giá_trị_tìm nằm trong cột đầu tiên của bảng này. Địa chỉ của bảng nên là tuyệt đối (có dấu $ phía trước dòng và cột bằng cách nhấn F4) để sao chép công thức vẫn hoạt động.
• thứ_tự_cột_cần_lấy_dữ_liệu: Đây là vị trí của cột bạn muốn lấy dữ liệu trong bảng tra cứu. Đếm từ bên trái của vùng bảng_dữ_liệu_cần_lấy đến cột bạn muốn lấy dữ liệu.
• kiểu_lấy_dữ_liệu: Đây là cách bạn muốn tìm kiếm, có thể là tương đối (sử dụng 1 hoặc True) hoặc tuyệt đối (sử dụng 0 hoặc False). Nếu chọn tương đối, bảng dữ liệu cần được sắp xếp tăng dần theo cột đầu tiên. Nếu chọn tuyệt đối, không cần phải sắp xếp bảng dữ liệu.

Cách sử dụng hàm Vlookup trong Excel

1. Phương pháp tìm kiếm Vlookup tương đối

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

Ví dụ 1: Xếp loại học lực cho học sinh trong danh sách dưới đây, sử dụng 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, sử dụng công thức sau:

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

Sau khi đó, nhấn Enter, sau đó kéo xuống các ô còn lại để nhận kết quả như dưới đây:

2. Phương pháp tìm kiếm Vlookup tuyệt đối

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

Để nhập 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à dưới đây là kết quả bạn sẽ nhận được:

Để điền thông tin Trình độ cho ô F23, áp dụng công thức sau:

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

Dưới đây là kết quả bạn sẽ nhận được:

Phương pháp sử 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ể điều chỉnh để tạo ra một hàm Vlookup linh hoạt hơn đặc biệt trong những tình huống có bảng dữ liệu tìm kiếm quá lớn và việc đếm cột trở nên... phiền toái.

Hàm Vlookup kết hợp Match

Hàm MATCH được sử dụng để tìm giá trị được chỉ định trong một phạm vi ô, 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 các 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 trong phạm vi A1:A3. Cú pháp của hàm Match như sau:

Và việc sử dụng hàm Match trong hàm Vlookup được thực hiện ở đâu? Bí mật đó là ở tham số thứ 3 trong hàm Vlookup: thứ_tự_cột_cần_lấy_dữ_liệu. Một ví dụ sẽ giúp bạn hiểu rõ hơn về điều này.

Ví dụ: Dưới đây là bảng dữ liệu:

Cần lọc dữ liệu theo từng tháng cho mỗi nhân viên trong bảng chính (cột tháng trong bảng chính có thể thay đổi). Dưới đây là bảng tính cụ thể:

Công thức sử dụng trong bài viết:

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

Hàm Match sẽ giúp bạn xác định vị trí của cột cần lấy dữ liệu trong bảng dữ liệu tìm kiếm (bảng phụ) của hàm Vlookup. Chỉ cần thay đổi dữ liệu Tháng trong ô B2, bạn sẽ nhận được dữ liệu chính xác cho từng tháng tương ứng. Ví dụ dưới đây minh họa việc thay đổi 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 các hàm LEFT, RIGHT, MID

Hàm Left/Right và Mid, cũng là một trong những hàm được ưa chuộng, có thể kết hợp với Vlookup để tạo ra những kết quả ấn tượng trong việc tìm kiếm.

Ví dụ: Trong bảng dưới đây, hãy điền vào các ô trống dựa trên các bảng phụ ở dưới:

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

Dưới đây là kết quả thu được:

Sử dụng VLOOKUP với 2 bảng tìm kiếm

Công thức tổng quát

=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)

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

=VLOOKUP(D5;IF(C4<2;bảng1;bảng2);2;TRUE)

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

Trong công thức trên, hàm IF được sử dụng như là đối số bảng_dữ_liệu_cần_lấy trong VLOOKUP, kiểm tra logic trên giá trị ở cột C “Năm” - biểu thị cho số năm kinh nghiệm của nhân viên. Khi C5 nhỏ hơn 2, Bảng1 sẽ trả về giá trị nếu đúng. Khi C4 lớn hơn hoặc bằng 2, Bảng2 sẽ trả về giá trị nếu sai.

Nói cách khác, nếu số năm kinh nghiệm ít hơn 2, thì dữ liệu sẽ được lấy từ Bảng1; ngược lại, nếu số năm kinh nghiệm là 2 hoặc lớn hơn, thì dữ liệu sẽ được lấy từ Bảng2.

Thay đổi Công thức

Nếu các bảng tra cứu đòi hỏi các quy tắc xử lý khác nhau, bạn có thể sử dụng hai hàm VLOOKUP 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 điều chỉnh dữ liệu đầu vào cho mỗi 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 Lỗi Không Tìm Thấy, thường xảy ra khi không có giá trị tương ứng được tìm thấy. Có thể là do 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 nhân gây ra lỗi này, ví dụ như giá_trị_tìm trong bảng chính và bảng phụ (trong ví dụ trên) khác nhau chỉ một khoảng trắng (dấu cách), hoặc bạn đã chọn sai phần vùng bảng_dữ_liệu_cần_lấy khiến cho giá_trị_tìm không nằm ở cột đầu tiên của bảng_dữ_liệu_cần_lấy...

Tùy vào từng trường hợp cụ thể mà bạn cần phải sửa lỗi #NA trong hàm Vlookup, nhưng nhớ rằng dữ liệu cần tìm phải chính xác đối với cột đầu tiên của bảng chứa giá trị tra cứu nếu sử dụng tìm kiế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.
• Chuyển kiểu_lấy_dữ_liệu sang FALSE để yêu cầu một kết hợp chính xác và True để cho phép kết hợp 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 kết hợp không chính xác sẽ khiến hàm VLOOKUP chọn giá trị gần nhất trong bảng mà nhỏ hơn giá trị tìm kiếm.
• Khi bỏ qua kiểu_lấy_dữ_liệu, hàm VLOOKUP sẽ cho phép kết hợp không chính xác nhưng vẫn sử dụng một kết hợp chính xác 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 sắp xếp theo thứ tự tăng dần. Nếu không, VLOOKUP có thể trả về giá trị không chính xác.
• Nếu kiểu_lấy_dữ_liệu là FALSE (cần kết hợp chính xác), bạn không cần phải sắp xếp các giá trị ở cột đầu tiên của bảng.

Bây giờ bạn đã biết cách sử dụng hàm Vlookup. Trong quá trình làm việc với Excel, bạn cũng có thể dùng hàm Index để trả về giá trị trong một mảng hoặc hàm Max, Min để tìm giá trị lớn nhất và nhỏ nhất.

Khi bạn đã thành thạo cú pháp và cách sử dụng các hàm trong Excel, bạn sẽ tiến bộ nhanh chóng hơn rất nhiều trong việc sử dụng Excel. Ngoài ra, còn rất nhiều hàm hữu ích khác, Mytour sẽ giới thiệu từng bước cho các bạn trong các bài viết sắp tới.

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