Cách sử dụng hàm MATCH và INDEX trong Excel

Trong Excel, việc truy xuất chính xác dữ liệu cụ thể thường là điều cần thiết. Mặc dù mỗi hàm INDEX và MATCH đều có điểm mạnh riêng nhưng việc kết hợp chúng sẽ mở ra một bộ công cụ mạnh mẽ để tra cứu dữ liệu. Chúng tạo điều kiện thuận lợi cho nhiều khả năng tìm kiếm, từ tra cứu ngang và dọc cơ bản đến các chức năng nâng cao hơn như tìm kiếm hai chiều, phân biệt chữ hoa chữ thường và đa tiêu chí. Cung cấp các khả năng nâng cao so với VLOOKUP, việc kết hợp INDEX và MATCH cho phép có nhiều tùy chọn tra cứu dữ liệu hơn. Trong hướng dẫn này, chúng ta hãy đi sâu vào những khả năng mà họ có thể cùng nhau đạt được.

Cách sử dụng INDEX và MATCH trong Excel

Trước khi chúng ta sử dụng hàm INDEX và MATCH, hãy đảm bảo rằng chúng ta biết cách INDEX và MATCH có thể giúp chúng ta tìm kiếm các giá trị như thế nào trước tiên.

Cách sử dụng hàm INDEX trong Excel

Sản phẩm INDEX hàm trong Excel trả về giá trị tại một vị trí nhất định trong một phạm vi cụ thể. Cú pháp của hàm INDEX như sau:

=INDEX(array, row_num, [column_num])

• array (bắt buộc) đề cập đến phạm vi mà bạn muốn trả về giá trị từ đó.
• ROW_NUM (bắt buộc, trừ khi cột_num có mặt) đề cập đến số hàng của mảng.
• column_num (tùy chọn, nhưng bắt buộc nếu ROW_NUM bị bỏ qua) đề cập đến số cột của mảng.

Ví dụ, để biết điểm số của Jeff, Các 6 sinh viên thứ ba trong danh sách, bạn có thể sử dụng hàm INDEX như thế này:

 Lưu ý: Phạm vi C2: C11 là nơi liệt kê điểm số, còn số 6 tìm điểm thi của 6 sinh viên thứ.

Sau đây chúng ta hãy làm một bài kiểm tra nhỏ. Đối với công thức =INDEX(A1:C1,2), nó sẽ trả về Ngày sinh, Các 2 giá trị thứ XNUMX trong hàng đã cho.

Bây giờ chúng ta nên biết rằng hàm INDEX có thể hoạt động hoàn hảo với phạm vi ngang hoặc dọc. Nhưng điều gì sẽ xảy ra nếu chúng ta cần nó để trả về một giá trị trong một phạm vi lớn hơn với nhiều hàng và cột? Trong trường hợp này, chúng ta nên áp dụng cả số hàng và số cột. Ví dụ, để tìm hiểu điểm số của Jeff trong phạm vi bảng thay vì một cột duy nhất, chúng ta có thể xác định điểm của anh ấy bằng một số hàng 6 và cột số 3 trong các tế bào từ A2 đến C11 như thế này:

Tuy nhiên, đối với một cơ sở dữ liệu thực sự lớn với nhiều hàng và cột, chắc chắn chúng ta không thể áp dụng công thức với số hàng và số cột chính xác. Và đây là lúc chúng ta nên kết hợp sử dụng hàm MATCH.

Cách sử dụng hàm MATCH trong Excel

Hàm MATCH trong Excel trả về một giá trị số, vị trí của một mục cụ thể trong phạm vi nhất định. Cú pháp của hàm MATCH như sau:

=MATCH(lookup_value, lookup_array, [match_type])

– lookup_value (bắt buộc) đề cập đến giá trị phù hợp trong tra cứu_array.

– lookup_array (bắt buộc) đề cập đến phạm vi ô mà bạn muốn MATCH tìm kiếm.

– Loại so khớp (không bắt buộc): 1, 0 or -1.

• 1 (mặc định), MATCH sẽ tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value. Các giá trị trong lookup_array phải được đặt theo thứ tự tăng dần.
• 0, MATCH sẽ tìm giá trị đầu tiên chính xác bằng lookup_value. Các giá trị trong lookup_array có thể theo thứ tự bất kỳ. (Đối với trường hợp loại đối sánh được đặt thành 0, bạn có thể sử dụng các ký tự đại diện.)
• -1, MATCH sẽ tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value. Các giá trị trong lookup_array phải được đặt theo thứ tự giảm dần.

Ví dụ, để biết vị trí của Vera trong danh sách những cái tên, bạn có thể sử dụng công thức MATCH như sau:

 Lưu ý: Kết quả “4” cho biết tên “Vera” đứng ở vị trí thứ 4 trong danh sách.

Bây giờ chúng ta đã biết về cách sử dụng cơ bản của các hàm INDEX và MATCH trong Excel, chúng ta hãy xắn tay áo lên và sẵn sàng kết hợp hai hàm.

Cách kết hợp INDEX và MATCH trong Excel

Vui lòng xem ví dụ dưới đây để tìm ra cách có thể kết hợp các hàm INDEX và MATCH:

Để tìm điểm số của Evelyn, với sự hiểu biết rằng điểm thi nằm trong 3 cột thứ XNUMX, chúng ta có thể sử dụng hàm MATCH để tự động xác định vị trí hàng mà không cần phải đếm thủ công. Sau đó, chúng ta có thể sử dụng hàm INDEX để truy xuất giá trị tại giao điểm của hàng được xác định và cột thứ 3:

Vì công thức có vẻ hơi phức tạp nên chúng ta hãy cùng tìm hiểu từng phần của nó nhé.

Công thức INDEX chứa ba đối số:

• ROW_NUM: MATCH(“Evelyn”,A2:A11,0) cung cấp cho INDEX vị trí hàng của giá trị “Evelyn” trong phạm vi A2: A11, Đó là 5.
• column_num: 3 chỉ định 3 cột thứ XNUMX cho INDEX để xác định điểm số trong mảng.
• array: A2: C11 hướng dẫn INDEX trả về giá trị khớp tại giao điểm của hàng và cột được chỉ định, trong phạm vi kéo dài từ A2 đến C11. Cuối cùng chúng ta nhận được kết quả 90.

Trong công thức trên, đã sử dụng một giá trị được mã hóa cứng, “Evelyn”. Tuy nhiên, trong thực tế, các giá trị được mã hóa cứng là không thực tế vì chúng sẽ yêu cầu sửa đổi mỗi khi tìm kiếm dữ liệu khác nhau, chẳng hạn như điểm của một học sinh khác. Trong những trường hợp như vậy, chúng ta có thể sử dụng tham chiếu ô để tạo công thức động. Ví dụ, trong trường hợp này, sẽ thay đổi “Evelyn” thành F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

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

Trong phần này, chúng ta sẽ nói về các trường hợp khác nhau để sử dụng các hàm INDEX và MATCH để đáp ứng các nhu cầu khác nhau.

INDEX và MATCH để áp dụng tra cứu hai chiều

Trong ví dụ trước, chúng ta đã biết số cột và sử dụng công thức MATCH để tìm số hàng. Nhưng nếu chúng ta cũng không chắc chắn về số cột thì sao?

Trong những trường hợp như vậy, chúng ta có thể thực hiện tra cứu hai chiều, còn được gọi là tra cứu ma trận, bằng cách sử dụng hai hàm MATCH: một hàm để tìm số hàng và hàm kia để xác định số cột. Chẳng hạn, để biết điểm số của Evelyn, chúng ta nên sử dụng công thức:

=INDEX(A2:C11,MATCH(“Evelyn”,A2:A11,0),MATCH(“Score”,A1:C1,0))

INDEX và MATCH để áp dụng tra cứu bên trái

Bây giờ, hãy xem xét một tình huống trong đó bạn cần xác định lớp của Evelyn. Bạn có thể nhận thấy rằng cột lớp được đặt ở bên trái cột tên, một tình huống vượt quá khả năng của một hàm tra cứu Excel mạnh mẽ khác, VLOOKUP.

Trên thực tế, khả năng thực hiện tra cứu bên trái là một trong những khía cạnh mà sự kết hợp giữa INDEX và MATCH vượt trội hơn VLOOKUP.

Để tìm Lớp của Evelyn, áp dụng công thức sau để tìm kiếm Evelyn ở B2:B11 và lấy giá trị tương ứng từ A2:A11.

=INDEX(A2:A11,MATCH(“Evelyn”,B2:B11,0))

INDEX và MATCH để áp dụng tra cứu phân biệt chữ hoa chữ thường

Các hàm MATCH vốn không phân biệt chữ hoa chữ thường. Tuy nhiên, khi bạn yêu cầu công thức của mình phân biệt được giữa các ký tự viết hoa và viết thường, bạn có thể nâng cao nó bằng cách kết hợp CHÍNH XÁC chức năng. Bằng cách kết hợp hàm MATCH với EXACT trong công thức INDEX, bạn có thể thực hiện tra cứu phân biệt chữ hoa chữ thường một cách hiệu quả, như minh họa bên dưới:

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))

• array đề cập đến phạm vi mà bạn muốn trả về giá trị.
• lookup_value đề cập đến giá trị cần khớp, xem xét trường hợp các ký tự, trong lookup_array.
• lookup_array đề cập đến phạm vi ô mà bạn muốn MATCH so sánh với lookup_value.

Ví dụ, để biết Điểm thi của JIMMY, sử dụng công thức sau:

=INDEX(C2:C11,MATCH(TRUE,EXACT(“JIMMY”,A2:A11),0))

INDEX và MATCH để tìm kết quả khớp gần nhất

Trong Excel, bạn có thể gặp phải tình huống cần tìm kết quả khớp gần nhất hoặc gần nhất với một giá trị cụ thể trong tập dữ liệu. Trong những trường hợp như vậy, việc sử dụng kết hợp các hàm INDEX và MATCH, cùng với các hàm ABS và MIN, có thể vô cùng hữu ích.

=INDEX(array, MATCH(MIN(ABS(lookup_array – lookup_value)), ABS(lookup_array – lookup_value),0))

• array đề cập đến phạm vi mà bạn muốn trả về giá trị.
• lookup_array đề cập đến phạm vi giá trị mà bạn muốn tìm kết quả phù hợp nhất lookup_value.
• lookup_value đề cập đến giá trị để tìm kết quả phù hợp nhất.

Ví dụ, để tìm hiểu có số điểm gần nhất với 85, sử dụng công thức sau để tìm kiếm điểm gần nhất tới 85 ở C2:C11 và lấy giá trị tương ứng từ A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

INDEX và MATCH để áp dụng tra cứu với nhiều tiêu chí

Để tìm một giá trị đáp ứng nhiều điều kiện, yêu cầu bạn tìm kiếm trên hai cột trở lên, hãy sử dụng công thức sau. Công thức cho phép bạn thực hiện tra cứu đa tiêu chí bằng cách chỉ định các điều kiện khác nhau trên các cột khác nhau, giúp bạn tìm thấy giá trị mong muốn đáp ứng tất cả các tiêu chí đã chỉ định.

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…), 0))

• array đề cập đến phạm vi mà bạn muốn trả về giá trị.
• (lookup_value=lookup_array) đại diện cho một điều kiện duy nhất. Điều kiện này kiểm tra xem một điều kiện cụ thể lookup_value phù hợp với các giá trị trong lookup_array.

Ví dụ, để tìm Điểm Coco lớp A sinh ngày 7/2/2008, bạn có thể sử dụng công thức sau:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX và MATCH để áp dụng tra cứu trên nhiều cột

Hãy tưởng tượng một tình huống trong đó bạn đang xử lý nhiều cột dữ liệu. Cột đầu tiên đóng vai trò là chìa khóa để phân loại dữ liệu ở các cột khác. Để xác định danh mục hoặc phân loại cho một mục cụ thể, bạn sẽ phải thực hiện tìm kiếm trên các cột dữ liệu và liên kết nó với khóa liên quan trong cột tham chiếu.

Ví dụ: trong bảng bên dưới, làm cách nào chúng ta có thể ghép học sinh Shawn với lớp tương ứng của cậu ấy bằng cách sử dụng INDEX và MATCH? Chà, bạn có thể đạt được nó bằng một công thức, nhưng công thức này khá rộng và có thể khó hiểu chứ chưa nói đến việc ghi nhớ và gõ.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(–($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(–($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), “”)

INDEX và MATCH để tra cứu giá trị không trống đầu tiên

Để truy xuất giá trị không trống đầu tiên, bỏ qua lỗi, từ một cột hoặc một hàng, bạn có thể sử dụng công thức dựa trên hàm INDEX và MATCH. Tuy nhiên, nếu bạn không muốn bỏ qua các lỗi trong phạm vi của mình, hãy thêm chức năng ISBLANK.

• Nhận giá trị không trống đầu tiên trong cột hoặc hàng bỏ qua lỗi:  

=INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Nhận giá trị không trống đầu tiên trong một cột hoặc hàng bao gồm các lỗi:

=INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

INDEX và MATCH để tra cứu giá trị số đầu tiên

Để truy xuất giá trị số đầu tiên từ một cột hoặc một hàng, hãy sử dụng công thức dựa trên các hàm INDEX, MATCH và ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX và MATCH để tra cứu các liên kết MAX hoặc MIN

Nếu bạn cần truy xuất một giá trị được liên kết với giá trị lớn nhất hoặc nhỏ nhất trong một phạm vi, bạn có thể sử dụng hàm MAX hoặc MIN cùng với các hàm INDEX và MATCH.

• INDEX và MATCH để truy xuất giá trị được liên kết với giá trị tối đa: 

=INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))

• INDEX và MATCH để truy xuất giá trị được liên kết với giá trị tối thiểu: 

=INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))

Có hai đối số trong các công thức trên:

– array đề cập đến phạm vi mà bạn muốn trả về thông tin liên quan.

– lookup_array đại diện cho tập hợp các giá trị cần kiểm tra hoặc tìm kiếm theo tiêu chí cụ thể, tức là giá trị tối đa hoặc tối thiểu.

Ví dụ, nếu bạn muốn xác định ai có điểm cao nhất, áp dụng công thức sau:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Trên đây là toàn bộ nội dung liên quan liên quan đến hàm INDEX và MATCH trong Excel. Hy vọng bạn thấy hướng dẫn này hữu ích và sẽ thực hiện nó thành công.