Cách ứng dụng hàm INDEX và MATCH nâng cao chi tiết, có ví dụ

Hàm INDEX và MATCH trong Excel cho phép tra cứu nâng cao và linh hoạt hơn, bao gồm tìm kiếm theo chiều ngang và dọc, tra cứu 2 chiều, tra cứu phía bên trái, phân biệt chữ hoa/thường và nhiều tiêu chí khác.

Hôm nay, chúng tôi sẽ hướng dẫn bạn hàm INDEX và MATCH nâng cao để giúp bạn tìm hiểu và thực hành cách ứng dụng chúng trong công việc hàng ngày. Cùng tìm hiểu nhé!

Những điều cơ bản về hàm INDEX và MATCH nâng cao

Bài viết hôm nay tập trung phân tích khía cạnh ưu việt của việc kết hợp hàm INDEX và MATCH thay cho VLOOKUP để là các hàm tìm kiếm nâng cao trong Excel.

Hàm INDEX

Tính năng: Hàm INDEX trả về giá trị của một ô trong bảng dựa trên số cột và hàng.

Công thức hàm:

=INDEX(array, row_num, [column_num])

Trong đó,

• array: Một dải ô mà bạn muốn trả về một giá trị
• row_num: Số thứ tự của hàng trong dải ô (array) mà bạn muốn trả về một giá trị. Nếu tham số này được bỏ qua, thì column_num là giá trị bắt buộc.
• colum_num: Số thứ tự của cột trong dải ô (array) mà bạn muốn giá trị được trả về. Nếu  tham số này được bỏ qua, thì row_num là là giá trị bắt buộc.

Lưu ý: Nếu cả 2 tham số row_num và colum_num đều được sử dụng, thì hàm INDEX sẽ trả về giá trị ở ô là giao điểm của hàng và cột xác định.

Ví dụ minh họa: 

Giả sử bạn nhập công thức hàm như sau: 

=INDEX(B4:F12,1,2)

Nghĩa là, công thức tìm kiếm dải ô B4:F12 rồi trả về giá trị của ô ở hàng thứ nhất (1) và cột thứ hai (2), cụ thể là ô C4.

Khá đơn giản, phải không? Tuy nhiên, trong thực tế, bạn không biết mình cần trả về giá trị ở hàng nào và cột nào. Do đó, hàm MATCH là lựa chọn kết hợp tuyệt vời.

Xem thêm: Giới thiệu các tính năng hữu dụng của hàm INDEX trong Excel

Hàm MATCH

Tính năng: Hàm MATCH tìm kiếm giá trị cần tìm trong dải ô, rồi trả về vị trí tương đối của ô đó trong dải ô.

Công thức hàm:

=MATCH(lookup_value, lookup_array, [match_type])

Trong đó,

• lookup_value: Chữ sô hay chuỗi ký tự mà bạn tìm kiếm. Đây có thể là một giá trị, một tham chiếu ô hay một giá trị logic.
• lookup_array: Dải ô được tìm kiếm
• match_type: Thông số này nói cho hàm MATCH biết bạn muốn trả về sự phù hợp tuyệt đối hay sự phù hợp tương đối. 

1 (Mặc định): Tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu. Các mảng tra cứu (array) phải được sắp xếp theo thứ tự tăng dần

0: Tìm giá trị đầu tiên chính xác bằng giá trị tra cứu. 

-1: Tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị lookup_value. Các giá trị trong mảng (array) cần tìm phải được sắp xếp theo thứ tự giảm dần.

Lưu ý: Lúc đầu, tính hữu ích của hàm MATCH có thể không rõ ràng. Chúng ta cần biết giá trị của ô, không phải vị trí nó trong dải dữ liệu.

Nhưng quan trọng là giá trị cần tìm kiếm (chẳng hạn là số thứ tự của cột hoặc hàng) có thể cung cấp mọi thông tin cần thiết cho hàm INDEX khi chúng ta tiến hành kết hợp 2 hàm này để xác định vị trí hàng và cột của một giá trị.

Ví dụ minh họa: 

Giả sử nếu dải ô B1:B3 chứa các giá trị “New-York”, “Paris”, “London”, và bạn sử dụng công thức:

=MATCH("London",B1:B3,0)

Nghĩa là, công thức sẽ trả kết quả về số 3 bởi vì “London” là mục thứ ba trong dải ô (array) tra cứu.

Cách sử dụng hàm index và match nâng cao trong Excel

Công thức hàm kết hợp:

=INDEX (column to return a value from, MATCH (lookup value, column to lookup against, 0))

Ví dụ minh họa:

Giả sử bạn có một danh sách dữ liệu và bạn cần tra cứu dân số của Nhật Bản:

Bạn sử dụng công thức:

=INDEX($D$2:$D$10,MATCH(“Nhật Bản”,$B$2:$B$10,0))

Trong đó,

• Hàm MATCH tìm kiếm giá trị cần tìm “Nhật Bản” ở cột B, chính xác hơn là dải ô B2:B10,  rồi trả về số 3, bởi vì “Nhật Bản” nằm thứ ba trong danh sách.
• Hàm INDEX lấy số 3 từ kết quả của hàm MATCH làm tham số thứ hai (row_num), rồi trở thành công thức đơn giản như sau =INDEX($D$2:$D$10,3)

Công thức trên sẽ tìm dân số trong dải ô D2:D10 rồi trả về giá trị của ô ở hàng thứ 3, cụ thể là ô D4, bởi vì chúng ta bắt đầu đếm từ hàng thứ 2.

Lưu ý: Số hàng và số cột trong mảng (array) của hàm INDEX nên lần lượt khớp với số hàng và số cột trong thông số row_num hay/và column_num trong hàm MATCH. Nếu không thì, công thức sẽ trả về kết quả không chính xác.

Tìm kiếm giá trị bên trái với hàm INDEX MATCH

Trong ví dụ này chúng ta sẽ tìm giá trị bên trái công thức với hàm INDEX MATCH.

Ví dụ: Bảng dưới đây có các thông tin về quốc gia, thủ đô của chúng và dân số trong thủ đô này. Hãy thiết lập công thức tra cứu thứ tự xếp hạng của ''Nga''

Bước 1. Điền công thức dùng hàm MATCH tìm vị trí của Nga:

= MATCH("Nga",$B$2:$B$10,0))

Bước 2. Xác định thông số cho hàm INDEX, tại ví dụ này, thông số là cột A (dải ô A2:A10)

Bước 3. Kết hợp hai bước trên lại, bạn có công thức sau:

= INDEX($A$2:$A$10,MATCH(“Nga”,$B$2:$B$10,0))

Mẹo: Việc sử dụng tham chiếu ô tuyệt đối luôn là một ý tưởng hay trong công thức INDEX và MATCH để dải ô cần tìm của bạn không bị phá hỏng khi bạn sao chép công thức sang ô khác.

Tính toán bằng hàm INDEX MATCH (AVERAGE, MAX, MIN)

Bạn có thể lồng các hàm khác vào hàm INDEX MATCH để tìm giá trị nhỏ nhất hay lớn nhất, hay giá trị gần với giá trị trung bình nhất trong dải. Dưới đây là một số ví dụ công thức cho bảng được dùng trong ví dụ trước:

Chức năng	Ví dụ công thức	Miêu tả	Kết quả được trả về
Min	=INDEX($C$2:$C$10, MATCH(MIN($D$2:I$10), $D$2:D$10, 0))	Tìm kiếm giá trị nhỏ nhất trong cột D rồi lấy một giá trị từ cột C trên cùng một hàng.	Bắc Kinh
Max	=INDEX($C$2:$C$10, MATCH(MAX($D$2:I$10), $D$2:D$10, 0))	Tìm kiếm giá trị lớn nhất ở cột D rồi lấy một giá trị từ cột C trên cùng một hàng.	Lima
Average	=INDEX($C$2:$C$10, MATCH(AVERAGE($D$2:D$10), $D$2:D$10, 1))	Tính giá trị trung bình trong dải ô D2:D10, tìm giá trị gần với giá trị trung bình nhất, rồi lấy giá trị tương ứng từ cột C.	Matxcơva

Để hiểu bản chất các hàm Excel , cách sử dụng và viết công thức, mời bạn đọc tham khảo khóa học sau của Gitiho:

Lưu ý:

Tham số thứ 3 (match_type) của hàm MATCH trong công thức trên thường là “1” hay “-1” . Nhưng nếu bạn không chắc mảng cần tìm có chứa giá trị bằng giá trị trung bình hay không, thì bạn có thể nhập “0” cho sự phù hợp tuyệt đối.

• Với 1: Các giá trị trong cột cần tìm phải được sắp xếp theo thứ tự tăng dần, và công thức sẽ trả về giá trị lớn nhất – giá trị này có thể nhỏ hơn hay bằng giá trị trung bình.
• Với -1: Các giá trị trong cột cần tìm phải được sắp xếp theo thứ tự giảm dần, và công thức sẽ trả về giá trị nhỏ nhất – giá trị này có thể lớn hơn hay bằng giá trị trung bình.

Tìm kiếm giá trị theo hàng và theo cột

Cú pháp hàm INDEX cũng cho phép thực hiện tra cứu ở giao điểm của cột và hàng. Công thức INDEX và MATCH để trả về số thứ tự của cột như sau:

= INDEX (lookup table, MATCH (vertical lookup value, column to lookup against, 0), MATCH (horizontal lookup value, row to lookup against, 0))

Ví dụ: Bảng dưới đây có thể hiện danh sách các thành phố đông dân nhất thế giới. Hãy tra cứu dân số của Mỹ năm 2021.

Bước 1. Viết 2 hàm MATCH trả về số thứ tự hàng và cột cho hàm INDEX của bạn.

=MATCH($H$3,$B$1:$B$11,0)

Công thức MATCH này trả về giá trị 4 vì “Mỹ” là giá trị thứ tư ở cột B (bao gồm cột tiêu đề).

=MATCH($H$4,$A$1:$E$1,0)

Công thức MATCH này trả về số “5” bởi vì “2021” nằm ở cột thứ năm.

Bước 2. Đặt các công thức trên vào hàm INDEX như sau:

= INDEX($A$1:$E$11, MATCH($H$3,$B$1:$B$11,0), MATCH($H$4,$A$1:$E$1,0))

Mẹo: Nếu bạn thay thế các hàm MATCH bằng các số được trả về, thì công thức sẽ dễ hiểu hơn rất nhiều:

= INDEX($A$1:$E$11, 4, 5, 0))

Có nghĩa là, nó trả về giá trị ở giao điểm của hàng thứ tư và cột thứ năm trong dải ô A1:E11, giá trị đó nằm ở ô E4.

Hàm IFERROR kết hợp INDEX và MATCH

Đôi khi bạn sẽ gặp lỗi #N/A hay lỗi #VALUE và lỗi báo giá trị không tồn tại trong mảng cần tìm, để khắc phục lỗi này bằng cái gì đó có nghĩa, bạn có thể lồng hàm INDEX kết hợp MATCH trong hàm IFERROR.

Công thức hàm:

=IFERROR(value, value_if_error)

Trong đó, 

• value: Là giá trị được kiểm tra lỗi (kết quả của công thức INDEX MATCH trong trường hợp này)
• value_if_error: Là giá trị cần trả về nếu công thức có lỗi.

Ví dụ: Thử nhập công thức dưới đây vào bảng:

=IFERROR(INDEX($A$1:$E$11, MATCH($G$2,$B$1:$B$11,0), MATCH($G$3,$A$1:$E$1,0)),"Không tìm thấy. Xin hãy thử lại!")

Lưu ý: Khi không muốn điền gì cả hoặc muốn để trống ô mà không tìm thấy giá trị tra cứu, bạn chỉ cần thay cụm từ (“Không tìm thấy. Xin hãy thử lại!”) thành (''").

Tổng kết

Qua bài viết này, Gitiho hy vọng bạn đọc có thể hiểu được cách sử dụng căn bản và kết hợp của hàm INDEX và MATCH nâng cao. 

Ngoài ra, để thành thạo các thủ thuật của hàm ứng dụng vào công việc hằng ngày bạn nên tham khảo các hàm VLOOKUP, IF, AND và luyện tập các bài tập Excel có đáp án được cung cấp.