Bỏ qua menu, vào nội dung chính

Hàm VLOOKUP trong Excel: công thức, 5 ví dụ nghiệp vụ và cách sửa lỗi #N/A

Hướng dẫn hàm VLOOKUP từ cú pháp tới năm ví dụ trên bảng dữ liệu văn phòng thật: tra đơn giá từ danh mục hàng, tra phụ cấp trong bảng lương, tính thưởng theo mốc doanh số, tra tên khách trong sổ công nợ. Kèm cách đọc và sửa lỗi #N/A, #REF!, lỗi dò sai kết quả và các mẹo cố định vùng.

12 phút đọc

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        4

Tạ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                         1395000

Muố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ên
excelC2: =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*C2
textKế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%         750000

Vớ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     15
excelB2: =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        30

Tê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)   -> 75000

Hà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

  1. Đị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.
  2. 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.
  3. 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).
  4. 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.
  5. 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.

  • Excel
  • VLOOKUP
  • Hàm Excel
  • Văn phòng

Nhận bài mới

Để lại email để nhận bài mới của series Excel cho dân văn phòng, hoặc theo dõi bằng RSS.

RSS Excel cho dân văn phòng

Email chỉ dùng để gửi bài mới, xem Chính sách bảo mật.

Trao đổi về dự án

Cần tư vấn?