Hàm INDEX MATCH trong Excel là cặp đôi mà nhiều người chuyển sang dùng sau khi gặp giới hạn của VLOOKUP. Khi bảng hay thay đổi, việc chèn thêm một cột có thể làm VLOOKUP trả sai dữ liệu mà không báo lỗi. Bài này giải thích từng hàm, cách ghép chúng và khi nào việc đổi cách làm đáng công.
INDEX và MATCH làm việc gì
Hai hàm này đứng riêng đều đơn giản. Sức mạnh nằm ở việc ghép chúng lại với nhau.
INDEX nhận một vùng dữ liệu và một số thứ tự, rồi trả về giá trị nằm ở vị trí đó. Ví dụ =INDEX(C2:C41, 5) trả về ô thứ năm trong vùng C2 đến C41, tức ô C6.
MATCH làm việc ngược lại. Nó nhận một giá trị cần tìm cùng một vùng, rồi trả về vị trí của giá trị trong vùng. Ví dụ =MATCH(“HS05”, A2:A41, 0) cho biết mã HS05 nằm ở vị trí thứ mấy trong cột A.
Khi đặt MATCH vào trong INDEX, MATCH tìm ra số thứ tự, còn INDEX dùng số thứ tự đó để lấy dữ liệu từ bất kỳ cột nào bạn muốn.
Cú pháp ghép hai hàm
Công thức chung là =INDEX(cột_kết_quả, MATCH(giá_trị_cần_tìm, cột_khoá, 0)). Tham số 0 ở cuối buộc Excel tìm khớp chính xác, tương tự tham số FALSE của VLOOKUP.
Ví dụ dưới đây dùng bảng điểm, với mã học sinh ở cột A và điểm Toán ở cột C. Công thức cần dùng là =INDEX($C$2:$C$41, MATCH(A2, $A$2:$A$41, 0)). Excel tìm vị trí của A2 trong cột A, rồi lấy giá trị cùng vị trí ở cột C.
Hai vùng được khai báo riêng rẽ, nên bạn không cần đếm số thứ tự cột như ở VLOOKUP. Nếu có người chèn thêm một cột vào giữa bảng, Excel tự điều chỉnh các vùng và công thức vẫn đúng.
Những lợi thế so với VLOOKUP
Sau nhiều lần dùng thực tế, có ba lợi thế rõ rệt nhất.
Thứ nhất, INDEX MATCH tra cứu được về bên trái. Giả sử cột mã nằm ở cột D còn tên nằm ở cột B. VLOOKUP không thể lấy tên từ một cột nằm trước cột khoá, nhưng INDEX MATCH làm được ngay vì hai vùng độc lập.
Thứ hai, công thức an toàn hơn khi cấu trúc bảng đổi. Chèn cột, xoá cột hay đổi chỗ cột không khiến công thức trả sai dữ liệu một cách âm thầm.
Thứ ba, tốc độ tốt hơn trên bảng lớn. Với hàng chục nghìn dòng và nhiều công thức, INDEX MATCH thường nhẹ hơn vì chỉ xử lý đúng hai cột thay vì cả bảng.
VLOOKUP vẫn có chỗ đứng. Với bảng nhỏ, cố định, người đọc quen VLOOKUP hơn, và công thức ngắn gọn hơn một chút. Chọn công cụ nào tuỳ vào bảng của bạn. Nếu cần ôn lại hàm cũ, bài hướng dẫn hàm VLOOKUP trong Excel sẽ giúp bạn nhớ lại nhanh.
Tra cứu theo hai điều kiện
Đây là tình huống VLOOKUP gần như không xử lý được. Giả sử bạn có bảng điểm nhiều học kỳ, mỗi học sinh xuất hiện hai lần: một dòng cho học kỳ 1 và một dòng cho học kỳ 2. Muốn lấy điểm của HS05 ở học kỳ 2, bạn cần khớp cả mã lẫn học kỳ.
Cách làm là ghép hai điều kiện bên trong MATCH: =INDEX($C$2:$C$81, MATCH(1, ($A$2:$A$81=”HS05″)*($B$2:$B$81=”HK2″), 0)). Phép nhân hai điều kiện cho ra mảng các giá trị TRUE hoặc FALSE, khi Excel chuyển chúng thành 1 hoặc 0. MATCH tìm số 1 đầu tiên, tức dòng thoả cả hai điều kiện.
Với phiên bản Excel cũ, bạn phải nhấn Ctrl + Shift + Enter để công thức chạy như công thức mảng. Từ các bản Microsoft 365 trở đi, chỉ cần Enter. Nếu thấy khó hiểu, hãy đọc bài công thức mảng trong Excel cho người mới trước, rồi quay lại bài này.
Các lỗi hay gặp
Lỗi #N/A xuất hiện khi MATCH không tìm thấy giá trị. Nguyên nhân thường là dư khoảng trắng hoặc sai kiểu dữ liệu giữa số và văn bản, giống trường hợp của VLOOKUP.
Lỗi #REF! xuất hiện khi số thứ tự MATCH trả về vượt ra ngoài vùng của INDEX. Điều này thường do hai vùng không cùng kích thước, hoặc do bạn khai báo vùng kết quả và vùng khoá lệch dòng. Hãy giữ hai vùng cùng dòng đầu và dòng cuối.
Kết quả sai mà không báo lỗi thường do quên tham số 0 ở cuối MATCH. Khi đó Excel tìm khớp gần đúng và cho ra một dòng bất kỳ. Nên luôn gõ số 0 rõ ràng.
Bắt đầu từ đâu để đỡ rối
Nếu chưa dùng INDEX MATCH bao giờ, đừng viết công thức dài ngay. Hãy gõ MATCH riêng ở một ô để xem số thứ tự nó trả về. Sau đó gõ INDEX riêng với số đó. Khi cả hai chạy đúng, hãy ghép lại. Cách tách từng lớp này giúp bạn xác định lỗi nằm ở đâu.
Tài liệu của Microsoft về công thức mảng và hàm VLOOKUP có thêm các biến thể mà bài này chưa đề cập.
Việc nhỏ để bạn tự làm
Hãy lấy bảng điểm bạn đã làm ở bài VLOOKUP, đổi thứ tự cột sao cho cột mã nằm bên phải cột tên. Thử lấy tên bằng VLOOKUP và xem nó có làm được không, rồi viết lại bằng INDEX MATCH. Sau đó chèn thêm một cột trống vào giữa bảng và kiểm tra xem công thức còn đúng không. Hai thử nghiệm đơn giản này cho bạn thấy rõ vì sao nhiều người đã chuyển sang cặp hàm này.
