Hàm Index và Match là hai hàm rất đặc biệt trong Excel mà hoàn toàn có thể thay thế được cho hàm VLOOKUP. Để nắm rõ hơn cách sử dụng cũng như các ứng dụng của hai hàm này, chúng ta sẽ cùng luyện tập cách sử dụng hàm INDEX và MATCH thông qua các bài tập kèm theo lời giải nhé.

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

Cho bảng tính sau:

Nếu muốn biết thông tin của người có tên “Lê Thị Thủy” thì bạn phai làm thế nào? Chắc hẳn việc đầu tiên bạn cần xác định xem người đó nằm ở vị trí thứ mấy trong bảng tính này.

Bằng cách sử dụng hàm MATCH, bạn sẽ biết được 1 người nào đó nằm ở dòng thứ mấy 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
• cột Họ tên là vùng C3:C8

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

Tham khảo: Cách sử dụng hàm MATCH trong Excel

Vậy việc tìm ra số 3 thì có ý nghĩa gì? Hay nói cách khác biết người đó ở vị trí dòng thứ 3 trong bảng thì có tác dụng gì?

Để trả lời câu hỏi này, chúng ta xem tiếp yêu 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, ta thấy tại vị trí dòng 3, trong cột Email sẽ xuất hiện Email cần tìm. Như vậy có thể hiểu: Chỉ cần dóng theo cột Email, tại vị trí dòng 3 trong bảng là chúng ta có kết quả cần tìm.

Điều đó chính là cách kết hợp hàm INDEX với MATCH:

Trong công thức tại ô D15 tìm Email, chúng ta thấy:

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

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

• Chỉ ra cột chứa giá trị cần tìm là cột E, vùng E3:E8
• Chỉ ra dòng chứa giá trị cần tìm, dòng này đã xác định bởi hàm MATCH ở trên

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

Tham khảo: 6 tính năng hiệu quả nhất của hàm INDEX trong Excel

Bài tập 3: Dò tìm từ phải qua trái với hàm INDEX kết hợp MATCH

Thông thường chúng ta hay dò tìm theo chiều từ Trái qua Phải. Nhưng nếu phải tìm từ Phải qua Trái (ngược lại với thông thường) thì làm thế nào?

Ví dụ như sau: Tìm tên dựa vào thông tin Email?

Cột Email là cột thứ 3, nằm bên phải cột Họ tên (cột thứ 1).

Như vậy để dò tìm họ tên dựa vào Email tức là chúng ta đang tìm từ Phải qua trái.

Quá trình thực hiện như sau:

• Dùng hàm MATCH để tìm vị trí dòng của email trong cột Email =MATCH(Email, vùng chứa Email, 0)
• Kết hợp với hàm INDEX trong việc chỉ ra vị trí cột chứa kết quả cần tìm, là cột Họ tên (cột C), vùng C3:C8

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

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

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

Ví dụ ta có bảng đơn giá như sau:

Làm cách nào xác định được đơn giá của 1 mã hàng bất kỳ, trong 1 công ty bất kỳ?

Điều này tương đương việc chúng ta phải xét đồng thời 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 hàm Match như sau:

Bước 1: Xác định vị trí Mã hàng nằm trên cột thứ mấy trong bảng

• Tham chiếu giá trị Mã hàng trong dòng 3 (dòng chứa thông tin mã hàng làm căn cứ đối chiếu)
• Hàm MATCH trả về kết quả là 2, tương ứng với cột thứ 2

=MATCH(H2,A3:E3,0)

Bước 2: Xác định vị trí Mã công ty nằm trên dòng thứ mấy trong bảng

• Tham chiếu giá trị Mã công ty trong cột A (cột chứa thông tin mã công ty làm căn cứ đối chiếu)
• Hàm MATCH trả về kết quả là 3, tương ứng với dòng thứ 3

=MATCH(H3,A3:A7,0)

Như vậy giao điểm của dòng 3, cột 2 chính là ô B5 (bởi bảng bắt đầu tính từ dòng 3 trở đi, nên dòng 5 ứng với vị trí dòng thứ 3 của bảng đơn giá)

Bước 3: Xác định kết quả đơn giá cần tìm với hàm INDEX

Việc còn lại là dùng hàm INDEX để kết nối giữa Dòng và Cột đã xác định được ở trên.

Trong hàm INDEX này ta có:

• Thay vì tham chiếu tới 1 cột cố định tại tham số đầu tiên, chúng ta tham chiếu cho cả vùng bảng A3:E7.
• Trong bảng này, số dòng xác định bởi hàm MATCH tại ô I3, số cột xác định bởi hàm MATCH tại ô I2.
• Kết quả của hàm INDEX là giao điểm giữa số dòng và số cột đã xác định được.

=INDEX(A3:E7,I3,I2)

Kết luận

Như vậy thông qua 4 bài tập ví dụ, chúng ta đã có thể hình dung được cách sử dụng hàm INDEX kết hợp hàm MATCH để thực hiện các yêu cầu dò tìm, tham chiếu, tìm kiếm trong Excel. Khi ứng dụng tốt cách làm này, bạn hoàn toàn có thể thực hiện được những yêu cầu tìm kiếm phức tạp mà hàm VLOOKUP khó đáp ứng được.

Thay vì phải đắn đo sử dụng hàm VLOOKUP hay HLOOKUP, làm công thức dài dòng khi phải kết hợp VLOOKUP với hàm MATCH, HLOOKUP với hàm MATCH, thì chúng ta chỉ cần dùng INDEX kết hợp MATCH là xong.

Ngoài ra các bạn có thể đọc thêm một số bài viết thú vị khác:

Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel

Phân biệt hàm VLOOKUP với HLOOKUP và bí quyết sử dụng hàm

Hàm vlookup trong Excel và các ứng dụng nâng cao thường gặp