INDEX MATCH Và VLOOKUP: Cách Dùng Và Sửa Lỗi #N/A

VLOOKUP là hàm tra cứu quen thuộc nhất, INDEX kết hợp MATCH là cách làm linh hoạt hơn. Cả hai đều chạy được trên mọi phiên bản Excel, kể cả bản 2016, nên đây là lựa chọn an toàn khi file sẽ được mở trên nhiều máy khác nhau.

Hàm VLOOKUP

=VLOOKUP(giá_trị_tìm, bảng_tra, số_thứ_tự_cột, [kiểu_khớp])

=VLOOKUP(E2, A2:D100, 3, FALSE)

Tìm giá trị E2 ở cột đầu tiên của bảng, trả về giá trị ở cột thứ ba tính từ cột đầu bảng.

Tham số cuối rất quan trọng: FALSE là khớp chính xác, TRUE là khớp gần đúng. Quên tham số này thì Excel mặc định là TRUE và cho kết quả sai một cách âm thầm. Luôn ghi rõ FALSE trừ khi bạn cố ý tra bảng bậc thang.

Ba hạn chế của VLOOKUP

Không tra được sang trái. Giá trị tìm bắt buộc nằm ở cột đầu tiên của bảng tra.

Phải đếm số thứ tự cột. Với bảng rộng thì đếm nhầm là chuyện thường. Tệ hơn, nếu ai đó chèn thêm một cột vào giữa bảng, mọi công thức VLOOKUP đều sai mà không báo lỗi.

Quét cả khối bảng. Với bảng lớn thì chậm hơn cần thiết.

INDEX kết hợp MATCH

Cách này tách việc tra cứu thành hai phần: MATCH tìm vị trí, INDEX lấy giá trị tại vị trí đó.

=INDEX(C2:C100, MATCH(E2, A2:A100, 0))

Đọc là: tìm E2 trong cột A, được vị trí thứ mấy thì lấy giá trị tương ứng ở cột C.

Ưu điểm so với VLOOKUP:

• Tra được cả sang trái, vì hai vùng độc lập nhau.
• Không phải đếm cột, nên chèn thêm cột không làm sai công thức.
• Chỉ đọc hai cột được chỉ định nên nhẹ hơn với bảng lớn.

Số 0 ở cuối MATCH nghĩa là khớp chính xác, tương đương FALSE của VLOOKUP. Đừng bỏ qua.

Tra theo hai chiều

INDEX nhận cả số dòng và số cột, nên tra được theo cả hàng lẫn cột:

=INDEX(B2:F100, MATCH(H1, A2:A100, 0), MATCH(H2, B1:F1, 0))

Tìm dòng theo H1, tìm cột theo H2, trả về ô giao nhau. Rất tiện cho các bảng ma trận như bảng giá theo sản phẩm và theo khu vực.

Sửa lỗi #N/A

Lỗi này nghĩa là không tìm thấy. Bốn nguyên nhân theo thứ tự phổ biến:

Khoảng trắng thừa. Một bên có dấu cách ở cuối, một bên không. Bọc hàm TRIM quanh giá trị tìm.

Khác kiểu dữ liệu. Một bên là số thật, một bên là số lưu dạng văn bản. Kiểm tra bằng căn lề: số thật căn phải, văn bản căn trái.

Ký tự ẩn. Dữ liệu sao chép từ web hay phần mềm khác thường mang theo ký tự không in được. Dùng hàm CLEAN kết hợp TRIM.

Quên cố định vùng tra. Kéo công thức xuống làm vùng trượt theo. Thêm dấu đô la: $A$2:$D$100.

Muốn thay lỗi bằng thông báo dễ hiểu, bọc IFERROR: =IFERROR(VLOOKUP(...), "Không tìm thấy"). Nhưng dùng cẩn thận, vì IFERROR che luôn cả những lỗi thật mà bạn cần biết.

Nếu Excel của bạn từ bản 2021 trở lên

Có hàm XLOOKUP làm được mọi thứ của INDEX MATCH nhưng viết ngắn hơn nhiều, và có sẵn tham số xử lý khi không tìm thấy. Xem hàm XLOOKUP trong Excel.

Tuy nhiên nếu file sẽ được mở trên máy dùng bản cũ, vẫn nên giữ INDEX MATCH vì XLOOKUP sẽ báo lỗi #NAME? ở đó. Với máy cấu hình thấp chỉ cần các hàm cơ bản, key Office 2016 vẫn đáp ứng đủ.

Câu hỏi thường gặp

INDEX MATCH có nhanh hơn VLOOKUP không?

Với bảng lớn thì có, vì nó chỉ đọc hai cột thay vì cả khối. Đây là một cách giảm tải file nặng, xem tối ưu file Excel nặng.

VLOOKUP trả về giá trị sai dù không báo lỗi?

Gần như chắc chắn do quên tham số FALSE ở cuối, hoặc do ai đó chèn thêm cột vào bảng tra.

Tra được nhiều kết quả cùng lúc không?

VLOOKUP và INDEX MATCH chỉ trả về kết quả khớp đầu tiên. Muốn lấy mọi kết quả, cần hàm lọc ở bản 2021 trở lên.