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. Cùng nhau, 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.

• Hàm INDEX
• Hàm MATCH
• Kết hợp INDEX và MATCH

• Tra cứu hai chiều
• Tra cứu bên trái
• Tra cứu phân biệt chữ hoa chữ thường
• Phù hợp nhất
• Tra cứu với nhiều tiêu chí
• Tra cứu trên nhiều cột
• Tra cứu giá trị không trống đầu tiên
• Tra cứu giá trị số đầu tiên
• Tra cứu các liên kết MAX hoặc MIN
• Mẹo: Điều chỉnh thông báo lỗi #N/A của riêng bạn

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:

• mảng (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.
• cột_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 6sinh viên thứ ba trong danh sách, bạn có thể sử dụng hàm INDEX như thế này:

=INDEX(C2:C11,6)

√ 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 6sinh 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ề giá trị nào? --- Vâng, nó sẽ trở lại Ngày sinh, Các 2giá 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:

=INDEX(A2:C11,6,3)

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.

Tuyệt vời! Sử dụng các tab hiệu quả trong Excel như Chrome, Edge, Firefox và Safari!
Tiết kiệm 50% thời gian của bạn và giảm hàng ngàn lần nhấp chuột cho bạn mỗi ngày!

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:

• lookup_value (bắt buộc) đề cập đến giá trị phù hợp trong tra cứu_array.
• tra cứu_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 tra cứu_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 tra cứu_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 tra cứu_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:

=MATCH("Vera",A2:A11,0)

√ 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 chúng tôi 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 3cộ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:

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

Chà, 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é.

Sản phẩm INDEX công thức 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.
• cột_num: 3 chỉ định 3cột thứ XNUMX cho INDEX để xác định điểm số trong mảng.
• mảng: 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, chúng tôi đã 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 chúng tôi 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, tôi 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:

• mảng đề 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 tra cứu_array.
• tra cứu_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))

√ Lưu ý: Đây là công thức mảng yêu cầu bạn nhập với Ctrl + sự thay đổi + đăng ký hạng mục thi, ngoại trừ trong Excel 365 và Excel 2021.

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.

• mảng đề cập đến phạm vi mà bạn muốn trả về giá trị.
• tra cứu_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))

√ Lưu ý: Đây là công thức mảng yêu cầu bạn nhập với Ctrl + sự thay đổi + đăng ký hạng mục thi, ngoại trừ trong Excel 365 và Excel 2021.

Tuyệt vời! Sử dụng các tab hiệu quả trong Excel như Chrome, Edge, Firefox và Safari!
Tiết kiệm 50% thời gian của bạn và giảm hàng ngàn lần nhấp chuột cho bạn mỗi ngày!

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.

√ Lưu ý: Đây là công thức mảng yêu cầu bạn nhập với Ctrl + sự thay đổi + đăng ký hạng mục thi. Sau đó, một cặp dấu ngoặc nhọn sẽ hiển thị trên thanh công thức.

• mảng đề 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 tra cứu_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)), "")

Đó là nơi Kutools cho Excel's Lập chỉ mục và khớp trên nhiều cột tính năng có ích. Nó đơn giản hóa quy trình, giúp việc khớp các mục cụ thể với danh mục tương ứng của chúng một cách nhanh chóng và dễ dàng. Để mở khóa công cụ mạnh mẽ này và dễ dàng kết hợp Shawn với đẳng cấp của anh ấy, chỉ cần tải xuống và cài đặt bổ trợ Kutools cho Excel, rồi thực hiện như sau:

1. Chọn ô đích nơi bạn muốn hiển thị lớp phù hợp.
2. trên Kutools tab, nhấp vào Công thức trợ giúp > Tra cứu & Tham khảo > Lập chỉ mục và khớp trên nhiều cột.
3. 
3. Trong hộp thoại bật lên, hãy thực hiện như sau:
   1. Bấm vào cái thứ 1 nút bên cạnh tra cứu_col để chọn cột chứa thông tin chính bạn muốn trả về, tức là tên lớp. (Bạn chỉ có thể chọn một cột duy nhất ở đây.)
   2. Bấm vào thứ 2 nút bên cạnh Bảng_rng để chọn các ô để khớp với các giá trị trong tra cứu_col, tức là tên học sinh.
   3. Bấm vào cái thứ 3 nút bên cạnh Tra cứu_value để chọn ô chứa tên học sinh mà bạn muốn khớp với lớp của họ, trong trường hợp này là Shawn.
   4. Nhấp chuột OK.
   5. 

Kết quả

Kutools đã tự động tạo công thức và bạn sẽ thấy tên lớp của Shawn được hiển thị ngay trong ô đích.

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:
• • mảng đề cập đến phạm vi mà bạn muốn trả về thông tin liên quan.
  • tra cứu_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))

Mẹo: Điều chỉnh thông báo lỗi #N/A của riêng bạn

Khi làm việc với hàm INDEX và MATCH của Excel, bạn có thể gặp lỗi #N/A khi không có kết quả trùng khớp. Ví dụ: trong bảng bên dưới, khi cố gắng tìm điểm của một học sinh tên Samantha, lỗi #N/A xuất hiện do cô ấy không có trong tập dữ liệu.

Để làm cho bảng tính của bạn thân thiện hơn với người dùng, bạn có thể tùy chỉnh thông báo lỗi này bằng cách gói công thức INDEX MATCH trong hàm IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Trên đây là toàn bộ nội dung liên quan liên quan đến hàm INDEX và MATCH trong Excel. Tôi hy vọng bạn thấy hướng dẫn này hữu ích. Nếu bạn đang muốn khám phá thêm các mẹo và thủ thuật Excel, xin vui lòng nhấn vào đây để truy cập vào bộ sưu tập phong phú của chúng tôi gồm hơn hàng nghìn bài hướng dẫn.