INDEX MATCH là cách ghép hai hàm để dò tìm trong Excel: MATCH tìm xem giá trị nằm ở vị trí thứ mấy, INDEX lấy giá trị ở đúng vị trí đó trong cột kết quả. Cách ghép này làm được mọi việc của VLOOKUP, tra được sang bên trái, dò được theo cả dòng lẫn cột, và quan trọng nhất là chạy được trên gần như mọi phiên bản Excel, kể cả Excel 2010, 2016, 2019 vốn không có XLOOKUP.
Nếu tệp của bạn phải mở được trên máy của nhiều phòng ban với phiên bản khác nhau, INDEX MATCH vẫn là lựa chọn chắc chắn nhất. Bài viết đi từ cách hai hàm hoạt động riêng lẻ, sang ví dụ giả định ghép chúng lại, dò bảng hai chiều, dò theo nhiều điều kiện, rồi đến cách nhờ AI viết và đọc công thức, cùng lỗi lệch dòng làm sai số lặng lẽ.
INDEX và MATCH, mỗi hàm làm một việc
Hàm INDEX trả về giá trị ở một vị trí trong vùng. Cú pháp INDEX(vùng, số dòng, số cột). Ví dụ INDEX(B2:B200, 5) trả về giá trị ở dòng thứ năm của vùng B2:B200, tức ô B6. Nếu vùng chỉ có một cột, bạn bỏ qua số cột. Bản thân INDEX không biết tìm kiếm, nó chỉ biết đi đến đúng địa chỉ bạn chỉ.
Hàm MATCH làm phần tìm kiếm. Cú pháp MATCH(giá trị cần tìm, vùng tìm, kiểu khớp) và kết quả là một con số chỉ vị trí. Ví dụ mã NV07 nằm ở dòng thứ 12 của vùng A2:A200 thì MATCH trả về 12. Đối số kiểu khớp là chỗ nhiều người vấp: số 0 nghĩa là khớp chính xác; số 1, cũng là giá trị mặc định khi bỏ trống, nghĩa là tìm giá trị lớn nhất không vượt quá giá trị cần tìm và đòi vùng phải sắp xếp tăng dần; số -1 ngược lại, đòi vùng sắp xếp giảm dần. Với mã hàng, mã nhân viên, tên khách, bạn gần như luôn cần số 0.
Khi ghép lại, MATCH tìm vị trí, INDEX lấy giá trị. Công thức =INDEX(B2:B200, MATCH("NV07", A2:A200, 0)) đọc thành lời là: tìm NV07 trong cột A, thấy ở vị trí nào thì lấy giá trị ở vị trí đó trong cột B. Vì vùng tìm và vùng kết quả là hai tham chiếu riêng, cột kết quả nằm bên trái hay bên phải cột tìm đều không sao, và chèn thêm cột vào giữa bảng cũng không làm công thức trỏ sai.
Ví dụ giả định: lấy họ tên và phòng ban theo mã nhân viên
Phòng hành chính một công ty may ở Nghệ An có trang tính NhanSu: cột A là họ tên, cột B là phòng ban, cột C là ngày vào làm, cột D là mã nhân viên dạng chữ như NV0412. Bảng chấm công ở trang ChamCong chỉ có mã nhân viên ở cột A. Máy dùng Excel 2016, dấu phân cách là dấu chấm phẩy.
- Tại ô B2 của ChamCong, lấy họ tên: =INDEX(NhanSu!$A$2:$A$800;MATCH($A2;NhanSu!$D$2:$D$800;0)). Cột họ tên nằm bên trái cột mã, việc VLOOKUP không làm được nếu không sắp xếp lại bảng.
- Tại ô C2, lấy phòng ban bằng cùng công thức nhưng đổi vùng INDEX sang NhanSu!$B$2:$B$800. Phần MATCH giữ nguyên.
- Nếu cần lấy nhiều cột, đặt MATCH vào một cột phụ, chẳng hạn cột Z tên là ViTri, rồi các cột khác chỉ cần INDEX theo ô Z2. Mỗi dòng chỉ tìm một lần, tệp tính nhanh hơn và công thức ngắn hơn.
- Bọc lỗi cho mã không tồn tại bằng IFNA, ví dụ =IFNA(INDEX(...);"Không có mã"). IFNA chỉ bắt lỗi không tìm thấy, các lỗi khác vẫn hiện ra để bạn biết công thức đang hỏng.
- Kiểm bằng cách chọn ngẫu nhiên năm mã, tra tay bằng Ctrl+F trong trang NhanSu, so họ tên và phòng ban.
Dấu $ trong ví dụ được đặt có chủ ý: vùng NhanSu khoá cả dòng lẫn cột để không trượt khi kéo, còn $A2 chỉ khoá cột để khi kéo xuống, ô cần tìm đổi theo từng dòng. Nếu bạn nhờ AI viết, hãy nói rõ công thức sẽ kéo theo chiều nào để nó đặt dấu $ cho đúng.
Dò hai chiều: tra bảng có cả tiêu đề dòng lẫn tiêu đề cột
INDEX nhận cả số dòng lẫn số cột, nên chỉ cần hai hàm MATCH là bạn tra được một bảng hai chiều. Ví dụ giả định: một cửa hàng giao hàng ở Hải Phòng có bảng phí, các dòng từ A3 đến A12 là mức cân nặng, các cột từ B2 đến F2 là năm vùng giao, phần giữa từ B3 đến F12 là mức phí. Muốn biết phí cho một đơn với cân nặng ở ô H2 và vùng ở ô I2, công thức là =INDEX($B$3:$F$12, MATCH(H2,$A$3:$A$12,1), MATCH(I2,$B$2:$F$2,0)).
Bạn chú ý hai MATCH dùng hai kiểu khớp khác nhau, và đó là cố ý. Vùng giao là tên cố định nên phải khớp chính xác, dùng 0. Cân nặng là mức bậc thang: một đơn 2,7 kg rơi vào bậc bắt đầu từ 2 kg, nên dùng 1 để lấy mức lớn nhất không vượt quá 2,7. Muốn kiểu khớp 1 chạy đúng, cột cân nặng phải sắp xếp tăng dần và dòng đầu tiên phải là mức thấp nhất, thường là 0. Nếu đơn nhẹ hơn mức đầu tiên, MATCH trả #N/A.
Cách dò hai chiều này hữu ích cho rất nhiều bảng trong văn phòng: bảng hoa hồng theo doanh số và nhóm sản phẩm, bảng định mức vật tư theo mẫu và cỡ, bảng đơn giá theo loại khách và số lượng. Thay vì viết IF lồng nhiều tầng, bạn giữ quy tắc trong một bảng nhìn được, sửa được, và công thức chỉ việc tra.
Dò theo nhiều điều kiện: hai cách và khi nào dùng cách nào
Cách một: cột phụ ghép khoá. Thêm vào bảng tra một cột ghép các điều kiện, ví dụ =A2&"|"&B2 để ghép mã hàng với mã kho. Khi tra, MATCH tìm giá trị ghép tương ứng như H2&"|"&I2. Cách này chạy trên mọi phiên bản, dễ hiểu, dễ kiểm, nhược điểm là phải thêm cột vào bảng gốc. Ký tự ngăn cách như dấu gạch đứng giúp tránh trường hợp hai cặp khác nhau ghép ra cùng một chuỗi.
Cách hai: MATCH trên mảng điều kiện. Công thức dạng =INDEX(C2:C500, MATCH(1, (A2:A500=H2)*(B2:B500=I2), 0)). Phép so sánh tạo ra hai mảng đúng sai, phép nhân biến chúng thành mảng số 1 và 0, MATCH tìm số 1 đầu tiên. Không cần cột phụ, nhưng với Excel 2019 trở về trước bạn phải kết thúc bằng Ctrl+Shift+Enter thay vì Enter, nếu không công thức trả lỗi hoặc trả sai.
Chọn cách nào. Tệp nhiều người dùng, nhiều phiên bản, người tiếp quản không giỏi Excel: chọn cột phụ. Tệp cá nhân trên Microsoft 365: cách hai gọn hơn, hoặc dùng luôn XLOOKUP hay FILTER. Bảng hàng chục nghìn dòng: cột phụ thường tính nhanh hơn vì không phải dựng mảng cho từng ô công thức.
Khi có nhiều dòng cùng khớp. Cả hai cách chỉ trả về dòng khớp đầu tiên. Nếu bảng tra có thể có hai dòng trùng điều kiện, kết quả phụ thuộc vào thứ tự dòng và có thể sai mà không báo. Hãy đếm trước bằng COUNTIFS; nếu có giá trị lớn hơn 1, vấn đề nằm ở dữ liệu chứ không ở công thức.
So sánh INDEX MATCH, VLOOKUP và XLOOKUP
Bảng dưới giúp bạn chọn hàm dò tìm phù hợp với tình huống. Không có hàm nào thắng ở mọi tiêu chí; yếu tố quyết định thường là phiên bản Excel của những người cùng dùng tệp.
| Tiêu chí | VLOOKUP | INDEX MATCH | XLOOKUP |
|---|---|---|---|
| Phiên bản | Mọi phiên bản | Mọi phiên bản | Microsoft 365, Excel 2021 trở về sau, Excel trên web |
| Tra sang bên trái | Không | Có | Có |
| Chèn cột vào bảng tra | Dễ trỏ sai cột | Không ảnh hưởng | Không ảnh hưởng |
| Mặc định khi bỏ đối số khớp | Gần đúng, dễ sai lặng lẽ | MATCH mặc định gần đúng, phải ghi 0 | Chính xác |
| Dò hai chiều | Phải ghép thêm MATCH | Tự nhiên | Lồng hai XLOOKUP |
| Độ dễ đọc với người mới | Dễ | Trung bình | Dễ |
| Xử lý không tìm thấy | Bọc IFERROR hoặc IFNA | Bọc IFNA | Có sẵn đối số |
Hàng thứ tư của bảng là bẫy chung của VLOOKUP và MATCH: cả hai đều mặc định tìm gần đúng. Khi nhờ AI viết, hãy kiểm xem MATCH có số 0 ở cuối không. Thiếu số 0 trên một cột mã không sắp xếp, công thức vẫn trả về một giá trị nào đó, và đó là loại lỗi nguy hiểm nhất vì không có gì báo động.
Nhờ AI viết, đọc và sửa công thức INDEX MATCH
INDEX MATCH dài và lồng nhiều tầng nên là loại công thức AI giúp được nhiều, cả khi viết mới lẫn khi đọc công thức người khác để lại. Một vài cách làm hiệu quả:
- Khi xin công thức, nói rõ "chỉ dùng INDEX và MATCH vì tệp chạy trên Excel 2016" để AI không chuyển sang XLOOKUP.
- Mô tả vị trí vùng tìm và vùng kết quả bằng địa chỉ cụ thể, và nói rõ hai vùng bắt đầu cùng một dòng. Lệch dòng bắt đầu, ví dụ INDEX từ B2 nhưng MATCH từ A1, làm mọi kết quả lệch đi một dòng.
- Với công thức dò nhiều điều kiện, hỏi thêm "công thức này có cần Ctrl+Shift+Enter trên Excel 2016 không". AI thường nhắc nhưng không phải lúc nào cũng nhắc.
- Khi đọc công thức cũ, dán công thức và yêu cầu: "Giải thích từng MATCH đang tìm gì ở đâu và kiểu khớp là gì, nói rõ nếu có chỗ nào tìm gần đúng".
- Để tự kiểm một phần công thức, bấm vào thanh công thức, bôi đen riêng phần MATCH rồi nhấn F9, Excel hiện con số vị trí. Nhấn Esc để thoát mà không làm mất công thức.
- Che dữ liệu khi gửi ví dụ: chỉ cần mã giả như NV001, tên giả như Nhân viên A, đừng dán danh sách nhân sự thật.
Sau khi AI sửa, chạy lại bước kiểm năm dòng ngẫu nhiên. Công thức dò tìm sai thường sai ở số ít dòng đặc biệt, nên hãy cố ý chọn cả dòng đầu bảng, dòng cuối bảng và mã vừa mới thêm.
Đặt tên vùng và dùng Table để công thức tự giải thích
Công thức INDEX MATCH dài vì phải ghi hai ba địa chỉ vùng, mỗi địa chỉ kèm tên trang tính và dấu $. Sáu tháng sau, chính người viết cũng khó nhớ NhanSu!$D$2:$D$800 là cột gì. Có hai cách làm công thức dễ đọc hơn mà không đổi logic. Cách thứ nhất là đặt tên cho vùng: chọn vùng mã nhân viên, gõ tên như MaNV vào ô Name Box bên trái thanh công thức rồi nhấn Enter. Công thức trở thành =INDEX(HoTen, MATCH($A2, MaNV, 0)), đọc lên gần như câu nói thường.
Cách thứ hai là chuyển bảng tra thành Table bằng Ctrl+T và đặt tên Table. Khi đó công thức tham chiếu theo tên cột, ví dụ tblNhanSu[MaNV], và vùng tự mở rộng khi thêm nhân viên mới, bạn không phải sửa số 800 thành 900. Khi nhờ AI viết công thức, hãy cho nó biết tên vùng hoặc tên Table thay vì địa chỉ ô; câu trả lời sẽ ngắn hơn, ít lỗi lệch dòng hơn, và người tiếp quản tệp đọc hiểu được mà không cần mở từng trang tính để dò.
Câu hỏi thường gặp
INDEX MATCH có nhanh hơn VLOOKUP không?
Trên bảng nhỏ và vừa, khác biệt tốc độ không đáng kể. Trên bảng lớn, lợi thế của INDEX MATCH đến từ việc bạn có thể đặt MATCH vào một cột phụ rồi dùng lại cho nhiều cột kết quả, thay vì mỗi cột phải tìm lại từ đầu. Giới hạn vùng tìm thay vì chọn cả cột cũng giúp tính nhanh hơn.
Vì sao INDEX MATCH trả kết quả của dòng bên dưới?
Gần như chắc chắn vùng của INDEX và vùng của MATCH không bắt đầu cùng một dòng, ví dụ một vùng bắt đầu từ dòng 1 có tiêu đề và vùng kia bắt đầu từ dòng 2. Hãy kiểm hai vùng có cùng dòng đầu và cùng dòng cuối, rồi kéo lại công thức.
Có cần nhấn Ctrl+Shift+Enter cho mọi công thức INDEX MATCH không?
Không. Công thức INDEX MATCH thông thường với một điều kiện chỉ cần Enter. Chỉ dạng dò nhiều điều kiện có phép nhân các mảng mới cần Ctrl+Shift+Enter trên Excel 2019 trở về trước. Trên Microsoft 365 và Excel 2021, mảng được xử lý tự động.
MATCH với kiểu khớp 1 dùng trong trường hợp nào?
Dùng khi tra theo bậc thang như mức cân nặng, mức doanh số, khung điểm, và cột tìm đã sắp xếp tăng dần. MATCH trả về vị trí của mức lớn nhất không vượt quá giá trị cần tìm. Với mã hàng, tên người hay mọi giá trị định danh, luôn dùng kiểu khớp 0.
Dùng INDEX MATCH để lấy giá trị mới nhất của một mã được không?
MATCH chỉ trả về dòng khớp đầu tiên, nên với bảng ghi theo thời gian từ cũ đến mới nó lấy dòng cũ nhất. Muốn lấy dòng mới nhất, bạn có thể dùng LOOKUP với mẹo chia mảng, hoặc dùng XLOOKUP tìm từ dưới lên nếu phiên bản cho phép. Nhờ AI viết và kiểm trên một mã có nhiều dòng.