VLOOKUP là hàm được tìm nhiều nhất trong nhóm hàm Excel ở Việt Nam. Lý do dễ hiểu: gần như mọi bảng tính văn phòng đều có hai phần tách rời, một bảng danh mục (hàng hoá, nhân viên, khách hàng) và một bảng phát sinh (đơn hàng, bảng lương, sổ công nợ). VLOOKUP là cây cầu nối hai phần đó, để bạn chỉ gõ mã và Excel tự điền phần còn lại.
Bài này đi từ cú pháp tới năm ví dụ trên dữ liệu nghiệp vụ. Các bảng ví dụ được giữ nhỏ để bạn gõ lại trong vài phút. Mọi công thức và kết quả trong bài đã được tính lại bằng máy trước khi đăng.
Cú pháp hàm VLOOKUP
excel=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value: giá trị cần tìm, ví dụ mã hàng ở ô A2.
- table_array: vùng bảng chứa dữ liệu. Cột đầu tiên của vùng này BẮT BUỘC là cột chứa giá trị cần tìm.
- col_index_num: số thứ tự cột muốn lấy kết quả, đếm từ cột đầu của vùng (cột đầu là 1), không phải từ cột A của trang tính.
- range_lookup: FALSE (hoặc 0) là tìm chính xác; TRUE (hoặc 1, hoặc bỏ trống) là tìm gần đúng theo khoảng.
Chữ V là vertical: Excel dò từ trên xuống trong cột đầu tiên. Hàm HLOOKUP dò theo chiều ngang, ít dùng hơn.
Ví dụ 1: tra đơn giá từ danh mục hàng hoá
Đây là trường hợp phổ biến nhất. Trang tính DanhMuc chứa danh sách mặt hàng văn phòng phẩm với đơn giá. Trang DonHang chỉ gõ mã hàng và số lượng, đơn giá phải tự điền.
textTrang DanhMuc
A B C D
1 Mã hàng Tên hàng ĐVT Đơn giá
2 SP01 Giấy A4 Ram 75000
3 SP02 Bút bi Hộp 50000
4 SP03 Kẹp giấy Hộp 12000
5 SP04 Sổ tay Quyển 30000
6 SP05 Mực in Hộp 350000
Trang DonHang
A B C D
1 Mã hàng Số lượng Đơn giá Thành tiền
2 SP03 10
3 SP01 5
4 SP05 2
5 SP02 4Tại ô C2 của trang DonHang, gõ công thức rồi kéo xuống tới C5. Cột thành tiền chỉ là phép nhân.
excelC2: =VLOOKUP(A2, DanhMuc!$A$2:$D$6, 4, FALSE)
D2: =B2*C2
D6: =SUM(D2:D5)textKết quả
A B C D
2 SP03 10 12000 120000
3 SP01 5 75000 375000
4 SP05 2 350000 700000
5 SP02 4 50000 200000
6 1395000Muốn lấy tên hàng thay vì đơn giá, chỉ cần đổi số thứ tự cột từ 4 thành 2: =VLOOKUP(A2, DanhMuc!$A$2:$D$6, 2, FALSE) trả về "Kẹp giấy" cho mã SP03.
Để ý các dấu $ trong vùng bảng. Chúng cố định vùng DanhMuc!$A$2:$D$6 để khi kéo công thức xuống, vùng tra cứu không bị trượt theo. Quên dấu $ là nguyên nhân số một khiến vài dòng đầu đúng còn các dòng cuối báo #N/A. Mẹo nhanh: bôi đen vùng trong thanh công thức rồi bấm F4, Excel tự thêm đủ bốn dấu $.
Ví dụ 2: tra phụ cấp theo chức vụ trong bảng lương
Phòng nhân sự thường có một bảng phụ cấp theo chức vụ, và bảng lương tháng chỉ ghi chức vụ của từng người. Thay vì gõ tay từng khoản phụ cấp, dùng VLOOKUP để bảng lương tự cập nhật khi quy chế phụ cấp thay đổi.
textTrang PhuCap
A B
1 Chức vụ Phụ cấp
2 Nhân viên 500000
3 Trưởng nhóm 1500000
4 Trưởng phòng 3000000
Trang BangLuong
A B C
1 Tên Chức vụ Phụ cấp
2 An Trưởng nhóm
3 Bình Nhân viên
4 Chi Trưởng phòng
5 Dũng Nhân viênexcelC2: =VLOOKUP(B2, PhuCap!$A$2:$B$4, 2, FALSE)
C6: =SUM(C2:C5)Kết quả: An 1.500.000, Bình 500.000, Chi 3.000.000, Dũng 500.000, tổng phụ cấp 5.500.000. Khi công ty điều chỉnh phụ cấp trưởng nhóm, bạn chỉ sửa một ô ở trang PhuCap, toàn bộ bảng lương đổi theo.
VLOOKUP không phân biệt chữ hoa chữ thường: "trưởng nhóm" và "Trưởng nhóm" được coi là giống nhau. Nhưng nó phân biệt dấu tiếng Việt và khoảng trắng, nên "Truong nhom" hay "Trưởng nhóm" (hai dấu cách) sẽ không khớp. Với cột chức vụ, nên dùng danh sách thả xuống (Data Validation) để người nhập không gõ sai.
Ví dụ 3: tính thưởng theo mốc doanh số bằng tìm gần đúng
Đây là lúc tham số TRUE phát huy tác dụng. Giả sử quy chế thưởng: doanh số dưới 50 triệu không thưởng, từ 50 triệu thưởng 1%, từ 100 triệu thưởng 2%, từ 200 triệu thưởng 3%. Bảng mốc chỉ cần ghi điểm bắt đầu của mỗi bậc, xếp tăng dần.
textTrang MucThuong
A B
1 Doanh số từ Tỷ lệ thưởng
2 0 0%
3 50000000 1%
4 100000000 2%
5 200000000 3%excelC2: =VLOOKUP(B2, MucThuong!$A$2:$B$5, 2, TRUE)
D2: =B2*C2textKết quả
A B (doanh số) C (tỷ lệ) D (thưởng)
2 An 45000000 0% 0
3 Bình 120000000 2% 2400000
4 Chi 200000000 3% 6000000
5 Dũng 75000000 1% 750000Với TRUE, Excel tìm giá trị lớn nhất trong cột đầu mà vẫn nhỏ hơn hoặc bằng giá trị cần tìm. Doanh số 120 triệu không có trong bảng, Excel dừng ở mốc 100 triệu và trả về 2%. Doanh số đúng bằng 200 triệu khớp mốc 200 triệu, được 3%.
Ví dụ 4: tra tên khách trong sổ công nợ và xử lý mã chưa có
Sổ theo dõi công nợ thường ghi mã khách hàng, còn tên và thời hạn nợ lấy từ danh mục khách. Khi kế toán gõ một mã chưa được khai báo, VLOOKUP báo #N/A. Thay vì để lỗi lan sang các cột tổng cộng, bọc công thức bằng IFERROR để hiện một dòng nhắc dễ hiểu.
textTrang KhachHang
A B C
1 Mã KH Tên khách Hạn nợ (ngày)
2 KH01 Cửa hàng Minh Phát 30
3 KH02 Công ty Hoa Sen 45
4 KH03 Đại lý Phú Thành 15excelB2: =IFERROR(VLOOKUP(A2, KhachHang!$A$2:$C$4, 2, FALSE), "Chưa có trong danh mục")
C2: =IFERROR(VLOOKUP(A2, KhachHang!$A$2:$C$4, 3, FALSE), "")textKết quả
A B C
2 KH02 Công ty Hoa Sen 45
3 KH09 Chưa có trong danh mục
4 KH01 Cửa hàng Minh Phát 30Tên khách hàng trong ví dụ là tên giả định để minh hoạ. Lưu ý: IFERROR che MỌI loại lỗi, kể cả lỗi do chính bạn gõ sai công thức. Chỉ bọc IFERROR khi công thức đã chạy đúng với dữ liệu mẫu. Nếu chỉ muốn bắt riêng lỗi không tìm thấy, dùng IFNA thay cho IFERROR (có từ Excel 2013).
Ví dụ 5: vì sao nhìn thấy mã mà VLOOKUP vẫn báo #N/A
Đây là câu hỏi hay gặp nhất. Ô A2 hiện chữ SP01, danh mục cũng có SP01, nhưng công thức vẫn báo #N/A. Thủ phạm thường là một dấu cách thừa ở cuối, lọt vào khi dán dữ liệu từ phần mềm khác hoặc từ email.
excelÔ A2 chứa "SP01 " (có dấu cách cuối)
=VLOOKUP(A2, DanhMuc!$A$2:$D$6, 4, FALSE) -> #N/A
=VLOOKUP(TRIM(A2), DanhMuc!$A$2:$D$6, 4, FALSE) -> 75000Hàm TRIM xoá khoảng trắng thừa ở đầu, cuối và giữa chuỗi. Cách kiểm tra nhanh: gõ =LEN(A2) ở một ô trống. SP01 có 4 ký tự; nếu kết quả là 5 thì có ký tự thừa.
Thủ phạm thứ hai là số lưu dưới dạng chữ. Mã nhân viên 1001 ở bảng chấm công có thể là số, trong khi ở danh mục lại là chữ (thường có tam giác xanh ở góc ô). Với Excel, số 1001 và chữ "1001" là hai giá trị khác nhau. Cách sửa: chuyển cả hai cột về cùng một kiểu, hoặc ép kiểu ngay trong công thức bằng =VLOOKUP(A2&"", ...) khi danh mục là chữ, hoặc =VLOOKUP(VALUE(A2), ...) khi danh mục là số.
Bảng tra lỗi VLOOKUP thường gặp
- #N/A: không tìm thấy giá trị trong cột đầu. Kiểm tra khoảng trắng thừa (TRIM), số lưu dạng chữ, vùng bảng bị trượt do thiếu dấu $, hoặc mã thực sự chưa có trong danh mục.
- #REF!: số thứ tự cột lớn hơn số cột của vùng bảng. Vùng A2:D6 chỉ có 4 cột, gõ 5 sẽ báo #REF!.
- #VALUE!: số thứ tự cột nhỏ hơn 1, hoặc giá trị tìm dài quá 255 ký tự.
- Kết quả sai mà không báo lỗi: gần như chắc chắn do bỏ trống hoặc gõ TRUE ở tham số cuối khi tra mã. Đổi thành FALSE.
- Kết quả đúng rồi tự sai sau khi chèn cột: số thứ tự cột là con số cố định, chèn thêm một cột vào giữa bảng danh mục thì cột 4 không còn là cột đơn giá. Dùng MATCH để tính số cột tự động, hoặc chuyển sang INDEX MATCH hay XLOOKUP.
Mẹo dùng VLOOKUP cho gọn và bền
- Định dạng bảng danh mục thành Table (Ctrl+T) và đặt tên, ví dụ tblHang. Công thức thành =VLOOKUP(A2, tblHang, 4, FALSE), thêm mặt hàng mới thì vùng tự mở rộng, không cần sửa công thức.
- Lấy nhiều cột cùng lúc bằng cách kết hợp MATCH: =VLOOKUP($A2, tblHang, MATCH(C$1, tblHang[#Headers], 0), FALSE). Công thức tự tìm đúng cột theo tiêu đề, kéo sang phải không phải sửa.
- VLOOKUP chỉ tra được sang phải. Muốn trả về cột nằm bên trái cột mã, dùng INDEX MATCH (mọi phiên bản) hoặc XLOOKUP (Excel 365 và Excel 2021 trở lên).
- Với bảng hàng chục nghìn dòng, hàng nghìn công thức VLOOKUP tìm chính xác có thể làm file chậm. Khi đó nên tra một lần vào cột phụ rồi tham chiếu cột phụ, thay vì lặp VLOOKUP ở nhiều cột.
- Kiểm tra chéo trước khi gửi báo cáo: gõ =SUMPRODUCT(--ISNA(C2:C500)) ở một ô trống để biết còn bao nhiêu dòng chưa tra được. Kết quả khác 0 nghĩa là có mã chưa khớp cần xử lý.
Khi nào bảng tính không còn đủ
VLOOKUP giải quyết tốt việc nối danh mục với phát sinh trong một tệp. Dấu hiệu bảng tính bắt đầu quá tải thường không nằm ở công thức mà ở cách làm việc: nhiều người cùng sửa một tệp qua email hoặc ổ chung và không biết bản nào là bản cuối; danh mục hàng hoá có nhiều phiên bản ở nhiều tệp khác nhau; tồn kho hoặc công nợ phải cộng tay từ nhiều sheet vào cuối tháng; và mỗi lần ai đó chèn cột là một loạt công thức tra cứu lệch theo.
Khi đó dữ liệu danh mục cần một nơi lưu duy nhất, có phân quyền và lịch sử sửa đổi, và việc tra cứu diễn ra tự động trong phần mềm thay vì trong từng ô. Hai bài dưới đây giúp bạn xác định thời điểm chuyển và tiêu chí chọn.
Câu hỏi thường gặp
Công thức hàm VLOOKUP trong Excel viết thế nào?
=VLOOKUP(giá_trị_tìm, vùng_bảng, số_thứ_tự_cột, FALSE). Ví dụ =VLOOKUP(A2, DanhMuc!$A$2:$D$6, 4, FALSE) tìm mã ở ô A2 trong cột đầu của vùng A2:D6 trang DanhMuc và trả về giá trị ở cột thứ tư của vùng đó. Máy cài định dạng Việt Nam thay dấu phẩy bằng dấu chấm phẩy.
Hàm VLOOKUP trong Excel dùng để làm gì?
Để lấy thông tin từ một bảng danh mục sang bảng phát sinh dựa trên một mã chung: đơn giá theo mã hàng, phụ cấp theo chức vụ, tên khách theo mã khách, tỷ lệ thưởng theo mốc doanh số. Nhờ vậy bạn chỉ nhập mã, phần còn lại tự điền và tự cập nhật khi danh mục thay đổi.
Tại sao VLOOKUP báo lỗi #N/A dù dữ liệu có trong bảng?
Thường do ba nguyên nhân: giá trị có khoảng trắng thừa (sửa bằng TRIM), một bên là số còn bên kia là số lưu dạng chữ (đưa về cùng kiểu), hoặc vùng bảng bị trượt khi kéo công thức vì thiếu dấu $ (bấm F4 để cố định).
Tham số cuối của VLOOKUP nên để TRUE hay FALSE?
Tra mã, tra tên: luôn FALSE (hoặc 0). Tra theo khoảng như bậc thưởng, bậc chiết khấu: TRUE, và cột đầu của bảng mốc phải xếp tăng dần, bắt đầu từ mốc nhỏ nhất.
VLOOKUP có tra được giữa hai file Excel khác nhau không?
Có. Mở cả hai tệp, khi gõ công thức thì chuyển sang tệp danh mục và bôi đen vùng, Excel tự ghi đường dẫn dạng [DanhMuc.xlsx]Sheet1!$A$2:$D$6. Nhược điểm: đổi tên hoặc di chuyển tệp nguồn là liên kết hỏng, nên nếu dùng lâu dài hãy gộp danh mục vào cùng một tệp.
VLOOKUP khác XLOOKUP thế nào?
XLOOKUP chỉ có trong Excel 365 và Excel 2021 trở lên. Nó tra được cả sang trái, mặc định tìm chính xác, có sẵn tham số giá trị trả về khi không tìm thấy, và không bị lệch khi chèn cột. Nếu tệp phải mở được trên Excel 2016 hoặc 2019, hãy tiếp tục dùng VLOOKUP hoặc INDEX MATCH.





