Những đ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 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 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.
Cú pháp &đối số INDEX và MATCH
Đây là cách cả hai hàm cần được viết để Excel hiểu chúng:
=INDEX ( mảng , row_num , [ column_num ])
- mảng là 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_num là 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 column_num hiện tại.
- column_num là cột trong mảng mà từ đó trả về một giá trị, chẳng hạn như 1 hoặc 9. Đó là tùy chọn.
=TRẬN ĐẤU ( lookup_value , lookup_array , [ match_type ])
- lookup_value là giá trị bạn muốn đối sánh 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_array là 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_type có thể là -1 , 0 hoặc 1 . Nó chỉ định cách lookup_value được 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.
Sử dụng 1 hoặc -1 cho những thời điểm 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, có thể làm sai lệch kết quả nếu bạn thực sự muốn 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.
Ví dụ về 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 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 .
Ví dụ về 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ụ đơ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ề 3 kết quả là.
- =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 thấy tiếp theo giá trị lớn nhất nhỏ hơn hoặc bằng 14 , trong trường hợp này là 13 , ở vị trí 1 trong tổng số lookup_array .
- =MATCH (14, D1:D2, -1) giống với công thức ở trên 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 kiếm 13 trong hàng đầu tiên của trang tính, trả về 4 vì nó là mục thứ tư trong mảng này.
Ví dụ về INDEX-MATCH
Dưới đâ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 số thứ tự.
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 vào nhau, đây là điều đang xảy ra:
- MATCH (F1, A2:A5) đang tìm kiếm F1 giá trị (8795) trong tập dữ liệu A2:A5 . Nếu đếm ngược cột, chúng ta có thể thấy đó 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 kể từ 2 là những gì MATCH tìm thấy: INDEX (B2:B5, 2, [column_num]) .
- Kể từ column_num là tùy chọn, chúng tôi có thể xóa cái đó để còn lại cái này: INDEX (B2:B5,2) .
- Vì vậy, bây giờ, đây giống như một công thức INDEX bình thường, nơi chúng tôi đang tìm giá trị của mục thứ hai trong B2:B5 , là 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ừ Green các mặt hàng trong tháng 5 . Điều này thực sự tương tự với ví dụ trên, nhưng một công thức MATCH bổ sung được lồng trong INDEX.
- TRẬN ĐẤU (G1, A2:A13,0) là mục đầu tiên được giải quyết 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 .
- TRẬN ĐẤU (G2, B1:E1,0) là công thức MATCH 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ừ "Màu xanh lá cây") trong tiêu đề cột tại B1:E1 . Cái này giải quyết thành 3 .
- Giờ đây, chúng tôi có thể viết lại công thức INDEX như thế này để hình dung điều 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 MATCH và INDEX
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 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:if 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 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 lookup_value đối số if 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 (ví dụ: =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 có liên quan
Hàm MATCH tương tự như hàm LOOKUP, nhưng hàm MATCH trả về vị trí của mặt hàng thay vì chính mặt hàng đó.
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 đó.