Cách dùng hàm INDEX và MATCH trong Excel

Những điều cần biết

• Hàm INDEX có thể dùng riêng, nhưng lồng hàm MATCH bên trong tạo thành tra cứu nâng cao.
• Hàm lồng nhau này linh hoạt hơn VLOOKUP và có thể cho kết quả nhanh hơn.

Bài viết này giải thích cách dùng hàm INDEX và MATCH cùng nhau trong mọi phiên bản Excel, bao gồm Excel 2019 và Microsoft 365.

Hàm INDEX và MATCH là gì?

INDEX và MATCH là các hàm tra cứu (lookup) trong Excel. Mặc dù chúng là hai hàm hoàn toàn riêng biệt có thể dùng độc lập, nhưng cũng có thể kết hợp để tạo công thức nâng cao.

Hàm INDEX trả về một giá trị hoặc tham chiếu đến giá trị từ trong một phạm vi chọn cụ thể. Ví dụ, nó có thể dùng để tìm giá trị ở hàng thứ hai của tập dữ liệu, hoặc hàng thứ năm cột thứ ba.

Mặc dù INDEX có thể dùng một mình, lồng MATCH trong công thức làm nó hữu ích hơn. Hàm MATCH tìm kiếm một mục xác định trong phạm vi ô và trả về vị trí tương đối của mục đó trong phạm vi. Ví dụ, nó có thể dùng để xác định một tên cụ thể là mục thứ ba trong danh sách tên.

Cú pháp & Đối số của INDEX và MATCH

Đây là cách viết cả hai hàm để Excel hiểu:

=INDEX(array, row_num, [column_num])

• array là phạm vi ô mà công thức sẽ dùng. Có thể là một hoặc nhiều hàng và cột, như A1:D5. Bắt buộc.
• row_num là hàng trong array từ đó trả về giá trị, như 2 hoặc 18. Bắt buộc trừ khi column_num có mặt.
• column_num là cột trong array từ đó trả về giá trị, như 1 hoặc 9. Tùy chọn.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value là giá trị bạn muốn khớp trong lookup_array. Có thể là số, văn bản, hoặc giá trị logic được gõ thủ công hoặc tham chiếu qua ô. Bắt buộc.
• lookup_array là phạm vi ô để tìm kiếm. Có thể là một hàng đơn hoặc một cột đơn, như A2:D2 hoặc G1:G45. Bắt buộc.
• match_type có thể là -1, 0, hoặc 1. Nó chỉ định cách lookup_value khớp với giá trị trong lookup_array (xem bên dưới). 1 là giá trị mặc định nếu đối số này bị bỏ qua.

Dùng 1 hoặc -1 khi cần tra cứu xấp xỉ theo thang đo, như khi xử lý số và xấp xỉ được chấp nhận. Nhớ rằng nếu không chỉ định match_type, 1 sẽ là mặc định, có thể làm lệch kết quả nếu bạn thực sự muốn khớp chính xác.

Ví dụ công thức INDEX và MATCH

Trước khi xem cách kết hợp INDEX và MATCH thành một công thức, chúng ta cần hiểu cách các hàm này hoạt động riêng lẻ.

Ví dụ INDEX

=INDEX(A1:B2,2,2)
=INDEX(A1:B1,1)
=INDEX(2:2,1)
=INDEX(B1:B2,1)

Trong ví dụ đầu tiên này, có bốn công thức INDEX để lấy giá trị khác nhau:

• =INDEX(A1:B2,2,2) tìm trong A1:B2 giá trị ở cột 2 hàng 2, là Stacy.
• =INDEX(A1:B1,1) tìm trong A1:B1 giá trị ở cột 1, là Jon.
• =INDEX(2:2,1) tìm mọi thứ trong hàng 2 để lấy giá trị cột 1, là Tim.
• =INDEX(B1:B2,1) tìm trong B1:B2 giá trị ở hàng 1, là Amy.

Ví dụ MATCH

=MATCH("Stacy",A2:D2,0)
=MATCH(14,D1:D2)
=MATCH(14,D1:D2,-1)
=MATCH(13,A1:D1,0)

Dưới đây là bốn ví dụ dễ hiểu của hàm MATCH:

• =MATCH("Stacy",A2:D2,0) tìm Stacy trong phạm vi A2:D2 và trả về 3.
• =MATCH(14,D1:D2) tìm 14 trong D1:D2, nhưng vì không tìm thấy, MATCH tìm giá trị lớn nhất nhỏ hơn hoặc bằng 14, là 13, ở vị trí 1 của lookup_array.
• =MATCH(14,D1:D2,-1) giống công thức trên, nhưng vì mảng không theo thứ tự giảm dần như -1 yêu cầu, ta gặp lỗi.
• =MATCH(13,A1:D1,0) tìm 13 trong hàng đầu tiên, trả về 4 vì nó là mục thứ 4 trong mảng này.

Ví dụ INDEX-MATCH

Dưới đây là hai ví dụ kết hợp INDEX và MATCH trong một công thức:

Tìm tham chiếu ô trong bảng

=INDEX(B2:B5,MATCH(F1,A2:A5))

Ví dụ này lồng công thức MATCH trong INDEX. Mục tiêu là xác định màu món hàng dùng số món hàng.

Nếu nhìn hình ảnh, bạn thấy ở hàng "Separated" cách viết công thức riêng lẻ, nhưng vì chúng ta lồng chúng, đây là điều xảy ra:

• MATCH(F1,A2:A5) tìm giá trị F1 (8795) trong tập dữ liệu A2:A5. Nếu đếm xuống cột, thấy nó ở vị trí 2, đó là thứ MATCH tìm ra.
• Mảng INDEX là B2:B5 vì chúng ta cuối cùng tìm giá trị trong cột đó.
• Hàm INDEX giờ có thể viết lại như sau vì 2 là thứ MATCH tìm thấy: INDEX(B2:B5, 2, [column_num]).
• Vì column_num tùy chọn, ta bỏ đi để còn: INDEX(B2:B5,2).
• Giờ đây giống công thức INDEX bình thường tìm giá trị mục thứ 2 trong B2:B5, là red (đỏ).

Tra cứu theo hàng và cột tiêu đề

=INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0))

Trong ví dụ này của MATCH và INDEX, chúng ta làm tra cứu hai chiều (two-way lookup). Ý tưởng là xem chúng ta kiếm được bao nhiêu tiền từ món hàng Green (Xanh) trong tháng May (Tháng 5). Điều này rất giống ví dụ trên, nhưng một công thức MATCH thêm được lồng trong INDEX.

• MATCH(G1,A2:A13,0) là mục đầu tiên được giải quyết. Nó tìm G1 (từ "May") trong A2:A13 để lấy giá trị cụ thể. Chúng ta không thấy ở đây, nhưng nó là 5.
• MATCH(G2,B1:E1,0) là công thức MATCH thứ hai, khá giống cái đầu nhưng tìm G2 (từ "Green") trong tiêu đề cột ở B1:E1. Cái này ra 3.
• Ta có thể viết lại công thức INDEX như sau để hình dung: =INDEX(B2:E13,5,3). Nó tìm trong cả bảng B2:E13 hàng 5 cột 3, trả về $180.

Quy tắc MATCH và INDEX

Có vài điều cần nhớ khi viết công thức với các hàm này:

• MATCH không phân biệt chữ hoa/thường, nên chữ hoa và thường được xử lý như nhau khi khớp giá trị văn bản.
• MATCH trả về #N/A vì nhiều lý do: nếu match_type là 0 và lookup_value không tìm thấy; nếu match_type là -1 và lookup_array không theo thứ tự giảm dần; nếu match_type là 1 và lookup_array không theo thứ tự tăng dần; và nếu lookup_array không phải hàng đơn hoặc cột đơn.
• Bạn có thể dùng ký tự đại diện (wildcard) trong đối số lookup_value nếu match_type là 0 và lookup_value là chuỗi văn bản. Dấu hỏi khớp bất kỳ ký tự đơn nào và dấu sao khớp bất kỳ chuỗi ký tự nào (ví dụ: =MATCH("Jo*",1:1,0)). Để MATCH tìm dấu hỏi hoặc dấu sao thực tế, gõ ~ trước.
• INDEX trả về #REF! nếu row_num và column_num không trỏ đến ô trong array.

Các hàm Excel liên quan

Hàm MATCH tương tự LOOKUP, nhưng MATCH trả về vị trí của mục thay vì chính mục đó.

VLOOKUP là hàm tra cứu khác trong Excel, nhưng khác với MATCH cần INDEX cho tra cứu nâng cao, công thức VLOOKUP chỉ cần một hàm đó.