Hàm VLOOKUP tìm một giá trị ở cột đầu tiên của một bảng, rồi trả về dữ liệu nằm trên cùng hàng ở cột bạn chỉ định. Cú pháp: =VLOOKUP(giá_trị_tìm, vùng_bảng, số_thứ_tự_cột, kiểu_dò). Ví dụ =VLOOKUP(A2, $E$2:$G$100, 3, FALSE) nghĩa là: lấy mã ở A2, tìm trong cột E của vùng E2:G100, thấy rồi thì trả về giá trị ở cột thứ 3 của vùng (cột G), và chỉ chấp nhận khớp chính xác.
Phần lớn lỗi VLOOKUP của người mới đến từ bốn chỗ: quên đối số cuối FALSE (hoặc 0), quên khoá vùng bằng dấu $, giá trị cần tìm không nằm ở cột trái nhất, và dữ liệu có khoảng trắng thừa hoặc số lưu dạng chữ. Bài này dùng một ví dụ bảng giá cụ thể để đi qua từng chỗ, kèm cách dùng XLOOKUP cho ai có Excel bản mới.
Ví dụ cơ bản: lấy đơn giá từ bảng danh mục
Giả sử sheet DanhMuc có cột A là Mã hàng (SP01, SP02…), cột B là Tên hàng, cột C là Đơn giá, dữ liệu từ hàng 2 đến hàng 200. Ở sheet HoaDon, cột A bạn gõ mã hàng, muốn cột C tự hiện đơn giá. Tại ô C2 của sheet HoaDon, gõ: =VLOOKUP(A2, DanhMuc!$A$2:$C$200, 3, FALSE), Enter, rồi kéo công thức xuống các hàng dưới.
Đọc công thức theo lời: "Lấy giá trị ở A2, tìm nó trong cột đầu tiên của vùng A2:C200 bên sheet DanhMuc; khi tìm thấy, trả về ô ở cột thứ ba của vùng đó, tức Đơn giá; chỉ nhận khi trùng khớp hoàn toàn." Muốn lấy Tên hàng thay vì Đơn giá, chỉ cần đổi số 3 thành 2.
Lưu ý về dấu phân cách: nếu máy bạn đặt định dạng vùng là Việt Nam (dấu phẩy là dấu thập phân), Excel sẽ dùng dấu chấm phẩy giữa các đối số: =VLOOKUP(A2; DanhMuc!$A$2:$C$200; 3; FALSE). Gõ dấu phẩy mà Excel báo lỗi công thức thì gần như chắc là vì chuyện này. Cách xem: gõ =VLOOKUP( và đọc gợi ý cú pháp hiện ra bên dưới ô.
Bốn đối số của VLOOKUP và lỗi hay gặp ở từng cái
Hiểu đúng từng đối số thì tự sửa được phần lớn lỗi mà không phải tra mạng.
| Đối số | Ý nghĩa | Sai hay gặp |
|---|---|---|
| giá_trị_tìm | Thứ cần tìm: mã, tên, số CCCD… | Có khoảng trắng thừa, hoặc là số nhưng bảng nguồn lưu dạng chữ |
| vùng_bảng | Vùng chứa dữ liệu; cột giá trị tìm phải là cột trái nhất | Không khoá $, kéo xuống bị trượt vùng; chọn vùng bắt đầu sai cột |
| số_thứ_tự_cột | Đếm từ cột trái nhất của vùng, bắt đầu bằng 1 | Đếm theo chữ cái cột của sheet; số lớn hơn số cột của vùng gây #REF! |
| kiểu_dò | FALSE hoặc 0: khớp chính xác; TRUE hoặc bỏ trống: gần đúng | Bỏ trống nên Excel dò gần đúng, trả kết quả sai mà không báo lỗi |
Dấu $ trong $A$2:$C$200 là tham chiếu tuyệt đối: khi kéo công thức xuống, vùng không bị dời thành A3:C201, A4:C202… Cách nhanh: bôi đen phần vùng trong thanh công thức rồi bấm F4, Excel tự thêm dấu $. Nếu dữ liệu nguồn được định dạng thành Bảng (Ctrl + T), bạn có thể viết DanhMuc[#All] hay tên bảng, vùng tự mở rộng khi thêm hàng mới.
Sửa lỗi #N/A, #REF! và kết quả sai
#N/A: không tìm thấy. Kiểm tra thủ công bằng Ctrl + F xem mã có thật trong bảng nguồn không. Nếu có mà vẫn #N/A, thủ phạm thường là khoảng trắng: gõ =LEN(A2) so với số ký tự bạn thấy. Sửa bằng cách bọc TRIM: =VLOOKUP(TRIM(A2), …).
#N/A do số và chữ lệch nhau. Mã như 00123 hay số điện thoại thường bị lưu dạng chữ ở một bảng, dạng số ở bảng kia. Dấu hiệu là tam giác xanh nhỏ ở góc ô, hoặc số căn lề trái. Chuyển về cùng kiểu: chọn cột, bấm biểu tượng cảnh báo và chọn Chuyển thành số, hoặc dùng =VLOOKUP(A2&"", …) để ép giá trị tìm thành chữ.
#REF!: số cột vượt vùng. Vùng A:C chỉ có 3 cột mà bạn ghi 4. Mở rộng vùng hoặc sửa số cột.
Kết quả sai mà không báo lỗi. Gần như luôn do thiếu FALSE ở cuối. Khi bỏ trống, Excel hiểu là dò gần đúng và trả về giá trị của dòng "gần nhất" — trên bảng chưa sắp xếp, kết quả gần như ngẫu nhiên.
Hiện chữ dễ hiểu thay cho #N/A. Bọc IFERROR: =IFERROR(VLOOKUP(A2, $E$2:$G$100, 3, FALSE), "Chưa có mã"). Chỉ dùng khi đã chắc công thức đúng; bọc sớm quá sẽ che mất lỗi thật.
Dò tìm gần đúng: xếp loại điểm, tính hoa hồng theo bậc
Kiểu dò TRUE không phải vô dụng. Nó dành cho bảng theo ngưỡng. Ví dụ bảng xếp loại ở H2:I6: cột H là mức điểm thấp nhất của mỗi loại, sắp tăng dần (0, 5, 6.5, 8, 9), cột I là tên loại (Yếu, Trung bình, Khá, Giỏi, Xuất sắc). Công thức =VLOOKUP(B2, $H$2:$I$6, 2, TRUE) với B2 = 7.3 sẽ trả về "Khá", vì Excel tìm giá trị lớn nhất không vượt quá 7.3, là 6.5.
Điều kiện bắt buộc: cột đầu của bảng ngưỡng phải sắp xếp tăng dần và bắt đầu từ mức nhỏ nhất có thể (ở đây là 0). Nếu điểm nhỏ hơn ngưỡng đầu tiên, kết quả là #N/A. Cách này thay được cả chuỗi IF lồng nhau dài dòng như =IF(B2>=9,"Xuất sắc",IF(B2>=8,…)), và khi cần đổi ngưỡng bạn chỉ sửa bảng chứ không sửa công thức.
Ứng dụng tương tự: tính hoa hồng theo doanh số (dưới 50 triệu 2%, từ 50 triệu 3%…), tra phí vận chuyển theo cân nặng, tra thuế suất theo bậc. Luôn đặt bảng ngưỡng ở một vùng riêng có tiêu đề rõ, để người sau nhìn vào hiểu ngay.
Khi nào dùng XLOOKUP hoặc INDEX MATCH thay thế
VLOOKUP có hai giới hạn cố hữu: chỉ tìm được ở cột trái nhất, và số cột "cứng" nên chèn thêm cột vào giữa bảng là kết quả lệch. Excel đời mới có XLOOKUP khắc phục cả hai.
| Tiêu chí | VLOOKUP | XLOOKUP | INDEX + MATCH |
|---|---|---|---|
| Bản Excel | Mọi bản | Microsoft 365, Excel 2021 trở lên, Excel trên web | Mọi bản |
| Tìm sang cột bên trái | Không | Có | Có |
| Chèn cột vào bảng | Dễ lệch kết quả | Không ảnh hưởng | Không ảnh hưởng |
| Mặc định khi bỏ trống kiểu dò | Gần đúng (dễ sai) | Khớp chính xác | Tuỳ đối số của MATCH |
| Giá trị khi không thấy | Phải bọc IFERROR | Có sẵn đối số thứ tư | Phải bọc IFERROR |
Ví dụ cùng bài toán đơn giá bằng XLOOKUP: =XLOOKUP(A2, DanhMuc!A2:A200, DanhMuc!C2:C200, "Chưa có mã"). Bằng INDEX MATCH: =INDEX(DanhMuc!$C$2:$C$200, MATCH(A2, DanhMuc!$A$2:$A$200, 0)). Lưu ý quan trọng: tệp dùng XLOOKUP gửi cho người dùng Excel 2016 hoặc 2019 sẽ hiện lỗi #NAME?, nên nếu tệp đi nhiều nơi, VLOOKUP hoặc INDEX MATCH vẫn an toàn hơn. Google Sheets hỗ trợ cả ba hàm.
Ba thói quen để bảng tra cứu không bao giờ sai
Một: mã tra cứu phải duy nhất. VLOOKUP chỉ trả về dòng đầu tiên khớp; nếu bảng danh mục có hai dòng SP05 với hai giá khác nhau, bạn sẽ không bao giờ biết dòng thứ hai bị bỏ qua. Kiểm tra trùng bằng Định dạng có điều kiện, mục Giá trị trùng lặp, hoặc cột phụ =COUNTIF($A$2:$A$200, A2) và lọc ra các dòng lớn hơn 1.
Hai: để bảng nguồn ở sheet riêng, định dạng thành Bảng (Ctrl + T) và không gộp ô. Gộp ô làm vùng tra cứu thủng lỗ, sắp xếp lọc đều lỗi. Muốn tiêu đề nằm giữa nhiều cột, dùng Căn giữa vùng chọn (Center Across Selection) trong hộp Định dạng ô thay cho Gộp ô.
Ba: sau khi kéo công thức cho cả cột, kiểm tra chéo vài dòng bằng tay và đếm số ô lỗi bằng =COUNTIF(C2:C500, "#N/A") hoặc lọc cột theo giá trị lỗi. Một bảng 500 dòng có 3 dòng #N/A thường là ba mã gõ sai, sửa ngay lúc này rẻ hơn nhiều so với khi báo cáo đã gửi đi. Nếu tệp quá nặng vì hàng nghìn công thức tra cứu, sao chép cột kết quả rồi Dán đặc biệt dạng Giá trị khi dữ liệu đã chốt.
Luyện tay: dựng bảng thử trong 5 phút
Đọc xong mà không thử thì một tuần sau sẽ quên. Tạo một tệp nháp và làm theo các bước dưới; toàn bộ mất khoảng năm phút và bạn sẽ gặp đúng các lỗi đã nêu ở trên.
- Ở Sheet1, gõ tiêu đề A1 Mã, B1 Tên, C1 Giá. Từ hàng 2 nhập 5 dòng: SP01 Bút bi 5000, SP02 Vở 12000, SP03 Thước 8000, SP04 Tẩy 4000, SP05 Kéo 25000.
- Ở Sheet2, gõ A1 Mã, B1 Số lượng, C1 Đơn giá, D1 Thành tiền. Từ A2 nhập vài mã, cố ý gõ một mã không có (SP09) và một mã có khoảng trắng ở cuối ("SP02 ").
- Tại C2 gõ =VLOOKUP(A2, Sheet1!$A$2:$C$6, 3, FALSE), kéo xuống. Bạn sẽ thấy SP09 và "SP02 " cùng báo #N/A dù lý do khác nhau.
- Sửa công thức thành =VLOOKUP(TRIM(A2), …) để xử lý khoảng trắng; SP02 sẽ hiện giá, còn SP09 vẫn #N/A vì thật sự không có.
- Bọc IFERROR để SP09 hiện "Chưa có mã", rồi tại D2 gõ =B2*C2 tính thành tiền.
- Thử xoá dấu $ trong vùng rồi kéo lại công thức, quan sát các dòng dưới bị lệch; bấm Ctrl + Z để trả lại.
Làm xong bài này, bạn đã tự tay gặp ba lỗi phổ biến nhất của VLOOKUP và biết cách sửa từng cái — đó là phần kiến thức mà đọc lý thuyết khó nhớ được.
Câu hỏi thường gặp
VLOOKUP có tìm được từ phải sang trái không?
Không. VLOOKUP chỉ tìm ở cột trái nhất của vùng và trả về cột bên phải. Muốn tra ngược, dùng XLOOKUP nếu có Excel 2021 hoặc Microsoft 365, hoặc dùng INDEX kết hợp MATCH chạy được trên mọi bản. Cách thủ công là chèn một cột phụ bên trái chứa bản sao cột cần tìm.
VLOOKUP có phân biệt chữ hoa chữ thường không?
Không. Với VLOOKUP, sp01 và SP01 được xem là giống nhau. Nếu dữ liệu bắt buộc phân biệt hoa thường, phải dùng công thức kết hợp INDEX, MATCH và EXACT dạng mảng, phức tạp hơn nhiều. Trong thực tế nên chuẩn hoá mã về một kiểu viết ngay từ đầu.
Làm sao VLOOKUP theo hai điều kiện, ví dụ mã hàng và tháng?
Tạo cột phụ ở bên trái bảng nguồn ghép hai điều kiện, ví dụ =A2&"|"&B2, rồi VLOOKUP giá trị ghép tương ứng. Với Excel mới, XLOOKUP hoặc FILTER làm được trực tiếp mà không cần cột phụ. Nếu cần cộng tổng theo hai điều kiện thì SUMIFS phù hợp hơn tra cứu.
VLOOKUP sang một file Excel khác có được không?
Được. Mở cả hai tệp, khi gõ công thức thì chuyển sang tệp kia và chọn vùng, Excel tự ghi đường dẫn kèm tên tệp. Nhược điểm là khi đổi tên hoặc di chuyển tệp nguồn, liên kết bị gãy. Nếu dùng lâu dài, nên chép bảng nguồn vào cùng tệp ở một sheet riêng.
Vì sao kéo công thức VLOOKUP xuống thì các dòng dưới bị #N/A?
Thường do vùng bảng không được khoá bằng dấu đô la, nên khi kéo xuống vùng trượt theo và bỏ sót các dòng đầu của bảng nguồn. Bấm vào ô bị lỗi, xem thanh công thức; nếu vùng đã thành A5:C204 thay vì A2:C200 thì đúng là lỗi này. Sửa ô đầu bằng F4 rồi kéo lại.
VLOOKUP chậm với file lớn thì làm sao?
Thu hẹp vùng tra cứu đúng phạm vi dữ liệu thay vì chọn cả cột, chuyển bảng nguồn thành Bảng, và đổi các cột đã chốt sang giá trị tĩnh. Với hàng trăm nghìn dòng, cân nhắc Power Query để gộp bảng một lần thay vì để hàng nghìn công thức tính lại mỗi lần sửa ô.
