Rất Hay: Hướng dẫn cách dùng hàm INDEX và MATCH qua bài tập có lời giải

Hàm Index và Match là 2 hàm rất đặc biệt trong Excel có thể thay thế hoàn toàn hàm VLOOKUP. Để hiểu rõ hơn về công dụng và ứng dụng của hai hàm này, chúng ta cùng nhau thực hành cách sử dụng hàm INDEX và MATCH thông qua các bài tập có lời giải.

Bài tập 1: Sử dụng hàm MATCH tìm vị trí theo hàng, cột

Cho bảng sau: Muốn biết thông tin của người có tên “Lê Thị Thủy” thì phải làm sao? Có lẽ điều đầu tiên bạn cần xác định là người đó đang ở đâu trên biểu đồ này. Sử dụng hàm MATCH, bạn sẽ biết một người đang ở hàng nào trong bảng. Ta có công thức sau:

=MATCH(“Lê Thị Thủy”, cột Họ Tên, số 0)

• Tại ô D12 nhập tên Lê Thị Thủy
• Tên cột là phạm vi C3:C8

Như vậy, hàm MATCH trả về số 3, tương ứng với hàng thứ 3 trong bảng dữ liệu trên.

Vậy việc tìm số 3 có ý nghĩa gì? Nói cách khác, tác dụng của việc biết người đó ở vị trí hàng thứ ba trong bảng là gì? Để trả lời câu hỏi này chúng ta xem tiếp soi cầu dưới đây

Bài tập 2: Kết hợp hàm INDEX với MATCH để tìm Email theo tên

Khi nhìn vào bảng trên, chúng ta thấy rằng ở hàng 3, trong cột Email, Email sẽ được yêu cầu sẽ xuất hiện. Như vậy có thể hiểu: Chỉ cần căn thẳng hàng với cột Email, ở vị trí hàng 3 trong bảng, ta có kết quả cần tìm. Sau đây là cách kết hợp INDEX với MATCH: Trong công thức tại ô D15 để tìm Email, ta thấy:

=INDEX(E3:E8,MATCH(D12,C3:C8,0))

Công thức này, hàm INDEX có các nhiệm vụ sau:

• Cho biết cột chứa giá trị cần tìm là cột E, phạm vi E3:E8
• Hiển thị hàng chứa giá trị tìm kiếm, được chỉ định bởi hàm MATCH ở trên

Giao điểm giữa hàng và cột trong hàm INDEX là kết quả email cần tìm.

Bài 3: Tìm từ phải sang trái bằng hàm INDEX kết hợp MATCH

Thông thường chúng ta tìm kiếm theo hướng từ trái sang phải. Nhưng nếu bạn phải tìm từ phải sang trái (ngược lại với bình thường) thì sao? Ví dụ: Tìm tên dựa trên thông tin email? Cột Email là cột thứ 3, nằm bên phải cột Họ và Tên (cột 1). Như vậy để tìm tên dựa vào Email, tức là chúng ta tìm từ phải sang trái. Quá trình này như sau:

• Sử dụng hàm MATCH để tìm vị trí của hàng email trong cột Email =MATCH(Email, bộ chứa email, 0)
• Kết hợp với hàm INDEX để định vị cột chứa kết quả tìm kiếm, cột Tên (cột C), phạm vi C3:C8

=INDEX(C3:C8,MATCH(D12,E3:E8,0))

Rất đơn giản phải không nào.

Bài 4: Xác định đơn giá mặt hàng theo nhiều điều kiện

Ví dụ chúng ta có bảng giá như sau: Cách xác định đơn giá của từng mã hàng, tại từng công ty? Điều này tương đương với việc chúng ta cần xét cùng lúc 2 điều kiện Mã hàng và Mã công ty Để thực hiện yêu cầu này chúng ta sẽ sử dụng hàm Index kết hợp với hàm Match như sau: Bước 1: Tìm mã hàng ở cột nào trong bảng

• Tham khảo giá trị mã hàng tại dòng 3 (dòng chứa thông tin mã hàng làm căn cứ so sánh)
• Hàm MATCH trả về 2, tương ứng với cột thứ 2

=MATCH(H2,A3:E3,0)

Bước 2: Tìm mã công ty ở hàng nào trong bảng

• Tham khảo giá trị mã công ty tại cột A (cột chứa thông tin mã công ty làm cơ sở đối chiếu)
• Hàm MATCH trả về 3, tương ứng với hàng thứ 3

=MATCH(H3,A3:A7,0)

Vậy giao của hàng 3 cột 2 là ô B5 (do bảng bắt đầu từ hàng 3 trở đi nên hàng 5 tương ứng với vị trí hàng 3 của bảng đơn giá) Bước 3: Chỉ định đơn giá cần tra cứu bằng hàm INDEX Phần tiếp theo là sử dụng hàm INDEX để liên kết giữa các hàng và cột đã xác định ở trên. Trong hàm INDEX này, chúng ta có:

• Thay vì tham chiếu đến một cột cố định trong tham số đầu tiên, chúng ta tham chiếu đến toàn bộ vùng bảng A3:E7.
• Trong bảng này, số hàng do hàm MATCH xác định ở ô I3, số cột do hàm MATCH xác định ở ô I2.
• Kết quả của hàm INDEX là giao điểm của số hàng và số cột đã chỉ định.

=INDEX(A3:E7,I3,I2)

kết luận

Như vậy, qua 4 bài tập ví dụ, chúng ta đã có thể hình dung cách sử dụng hàm INDEX kết hợp với hàm MATCH để thực hiện tìm kiếm, tham chiếu và truy vấn trong Excel. Khi thực hiện tốt phương pháp này, bạn hoàn toàn có thể thực hiện các truy vấn tìm kiếm phức tạp mà hàm VLOOKUP khó có thể thực hiện được. Thay vì ngại sử dụng VLOOKUP hay hàm HLOOKUP, lập một công thức dài khi cần kết hợp VLOOKUP với hàm MATCH, HLOOKUP với hàm MATCH, chúng ta chỉ cần sử dụng INDEX kết hợp với MATCH. Ngoài ra, bạn có thể đọc thêm một số bài viết hấp dẫn khác:

Tại sao sử dụng INDEX và MATCH tốt hơn sử dụng VLOOKUP trong Excel

Phân biệt VLOOKUP với hàm HLOOKUP và cách sử dụng hàm

Hàm vlookup trong Excel và các ứng dụng nâng cao thông dụng