Cách Sử dụng Hàm INDEX và MATCH trong Excel

2024 Tác giả: Abigail Brown | [email protected]. Sửa đổi lần cuối: 2023-12-17 07:05

Điều cần biết

Hàm INDEX có thể được sử dụng một mình, nhưng việc lồng hàm MATCH vào bên trong nó sẽ tạo ra một tra cứu nâng cao.
Hàm lồng nhau này linh hoạt hơn hàm VLOOKUP và có thể mang lại kết quả nhanh hơn.

Bài viết này giải thích cách sử dụng đồng thời các hàm INDEX và MATCH trong tất cả các 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 trong Excel. Mặc dù chúng là hai hàm hoàn toàn riêng biệt có thể được sử dụng riêng, chúng cũng có thể được kết hợp để tạo ra các công thức nâng cao.

Hàm INDEX trả về một giá trị hoặc tham chiếu đến một giá trị từ bên trong một lựa chọn cụ thể. Ví dụ: nó có thể được sử dụng để tìm giá trị trong hàng thứ hai của tập dữ liệu hoặc trong hàng thứ năm và cột thứ ba.

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

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

Đây là cách cả hai hàm cần được viết để Excel hiểu chúng:

=INDEX(mảng, hàng_num, [cột_num])

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

=MATCH(tra cứu_giá_trị, tra cứu_mảng, [loại_đấu])

lookup_valuelà giá trị bạn muốn khớp trong lookup_array. Nó có thể là một số, văn bản hoặc giá trị lôgic được nhập theo cách thủ công hoặc được tham chiếu qua tham chiếu ô. Điều này là bắt buộc.
lookup_arraylà phạm vi ô cần xem qua. Nó có thể là một hàng hoặc một cột, chẳng hạn như A2: D2 hoặc G1: G45. Điều này là bắt buộc.
match_typecó thể là -1, 0 hoặc 1. Nó chỉ định cách lookup_value được so khớp với các 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.

Loại Kết hợp nào để Sử dụng
Loại Đối sánh	Chuyện gì	Quy tắc	Ví dụ
1	Tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu.	Các giá trị lookup_array phải được đặt theo thứ tự tăng dần (ví dụ: -2, -1, 0, 1, 2; hoặc A-Z;, hoặc FALSE, TRUE.	lookup_value là 25 nhưng nó bị thiếu trong lookup_array, vì vậy, thay vào đó, vị trí của số nhỏ nhất tiếp theo, như 22, được trả về.
0	Tìm giá trị đầu tiên chính xác bằng giá trị tra cứu.	Các giá trị lookup_array có thể theo bất kỳ thứ tự nào.	lookup_value là 25, vì vậy nó trả về vị trí là 25.
-1	Tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tra cứu.	Các giá trị lookup_array phải được đặt theo thứ tự giảm dần (ví dụ: 2, 1, 0, -1, -2).	lookup_value là 25 nhưng nó bị thiếu trong lookup_array, do đó, thay vào đó, vị trí của số lớn nhất tiếp theo, như 34, được trả về.

Sử dụng 1 hoặc -1 cho những lúc bạn cần chạy tra cứu gần đúng theo thang điểm, như khi xử lý các con số và khi tính gần đúng là ổn. Nhưng hãy nhớ rằng nếu bạn không chỉ định match_type, 1 sẽ là mặc định, điều này có thể làm sai lệch kết quả nếu bạn thực sự muốn có một kết quả khớp chính xác.

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

Trước khi xem xét 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 hoạt động của các hàm này.

INDEX Ví dụ

=INDEX (A1: B2, 2, 2)

=INDEX (A1: B1, 1)

=INDEX (2: 2, 1)=CHỈ SỐ (B1: B2, 1)

Trong ví dụ đầu tiên này, có bốn công thức INDEX mà chúng ta có thể sử dụng để nhận các giá trị khác nhau:

=INDEX (A1: B2, 2, 2)xem qua A1: B2 để tìm giá trị trong cột thứ hai và hàng thứ hai, là Stacy.
=INDEX (A1: B1, 1)xem qua A1: B1 để tìm giá trị trong cột đầu tiên, là Jon.
=INDEX (2: 2, 1)xem qua mọi thứ trong hàng thứ hai để tìm giá trị trong cột đầu tiên, đó là Tim.
=INDEX (B1: B2, 1)xem qua B1: B2 để tìm giá trị trong hàng đầu tiên, đó là Amy.

MATCH Ví dụ

=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ụ đơn giản về hàm MATCH:

=MATCH ("Stacy", A2: D2, 0)đang tìm kiếm Stacy trong phạm vi A2: D2 và trả về kết quả là 3.
=MATCH (14, D1: D2)đang tìm kiếm 14 trong phạm vi D1: D2, nhưng vì nó không được tìm thấy trong bảng, MATCH tìm giá trị lớn nhất tiếp theo nhỏ hơn hoặc bằng 14, trong trường hợp này là 13, nằm ở vị trí 1 của lookup_array.
=MATCH (14, D1: D2, -1)giống với 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, chúng tôi gặp lỗi.
=MATCH (13, A1: D1, 0)đang tìm 13 trong hàng đầu tiên của trang tính, trả về 4 vì đây là mục thứ tư trong mảng này.

Ví dụ về INDEX-MATCH

Đây là hai ví dụ mà chúng ta có thể 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 đang lồng công thức MATCH trong công thức INDEX. Mục đích là để xác định màu sắc của mặt hàng bằng cách sử dụng mã số mặt hàng.

Nếu bạn nhìn vào hình ảnh, bạn có thể thấy trong các hàng "Được phân tách" cách các công thức sẽ được viết theo cách riêng của chúng, nhưng vì chúng tôi đang lồng chúng nên đây là những gì đang xảy ra:

MATCH (F1, A2: A5)đang tìm giá trị F1 (8795) trong tập dữ liệu A2: A5. Nếu chúng ta đếm ngược cột, chúng ta có thể thấy nó là 2, vì vậy đó là những gì hàm MATCH vừa tìm ra.
Mảng INDEX là B2: B5 vì cuối cùng chúng tôi đang tìm kiếm giá trị trong cột đó.
Hàm INDEX hiện có thể được viết lại như thế này vì 2 là hàm MATCH được tìm thấy: INDEX (B2: B5, 2, [column_num]).
Vì cột_numlà tùy chọn, chúng ta có thể loại bỏ điều đó để còn lại như sau: INDEX (B2: B5, 2).
Vì vậy, bây giờ, đây giống như một công thức INDEX thông thường, trong đó chúng tôi đang tìm giá trị của mục thứ hai trong B2: B5, có màu đỏ.

Tra cứu Theo Tiêu đề Hàng và Cột

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

Trong ví dụ này về MATCH và INDEX, chúng tôi đang thực hiện tra cứu hai chiều. Ý tưởng là để xem chúng tôi đã kiếm được bao nhiêu tiền từ các mặt hàng Xanh trong tháng Năm. Điều này thực sự tương tự như ví dụ trên, nhưng một công thức MATCH bổ sung được lồng trong INDEX.

MATCH (G1, A2: A13, 0)là mục đầu tiên được giải trong công thức này. Nó đang tìm kiếm G1 (từ "May") trong A2: A13 để nhận một giá trị cụ thể. Chúng tôi không thấy nó ở đây, nhưng nó là 5.
MATCH (G2, B1: E1, 0)là công thức TRẬN ĐẤU thứ hai và nó thực sự giống với công thức đầu tiên nhưng thay vào đó là tìm kiếm G2 (từ "Green") trong tiêu đề cột tại B1: E1. Cái này giải quyết thành 3.
Bây giờ chúng ta có thể viết lại công thức INDEX như thế này để hình dung những gì đang xảy ra: =INDEX (B2: E13, 5, 3). Điều này đang tìm trong toàn bộ bảng, B2: E13, cho hàng thứ năm và cột thứ ba, trả về $ 180.

Quy tắc TRẬN ĐẤU và CHỈ SỐ

Có một số điều cần lưu ý khi viết công thức với các hàm này:

MATCH không phân biệt chữ hoa và chữ thường, vì vậy chữ hoa và chữ thường được xử lý giống nhau khi khớp các 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 được 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 tăng dần thứ tự, và nếu lookup_array không phải là một hàng hoặc một cột.
Bạn có thể sử dụng một ký tự đại diện trong đối số lookup_value nếu match_type là 0 và lookup_value là một chuỗi văn bản. Dấu chấm hỏi khớp với bất kỳ ký tự đơn nào và dấu hoa thị khớp với bất kỳ chuỗi ký tự nào (e.g., =MATCH ("Jo ", 1: 1, 0)). Để sử dụng MATCH để tìm dấu chấm hỏi hoặc dấu hoa thị thực tế, hãy nhập ~ trước.
INDEX trả về REF! nếu row_num và column_num không trỏ đến một ô trong mảng.

Các Hàm Excel Liên quan

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

VLOOKUP là một hàm tra cứu khác mà bạn có thể sử dụng trong Excel, nhưng không giống như MATCH yêu cầu INDEX để tra cứu nâng cao, các công thức VLOOKUP chỉ cần một hàm đó.