Hướng dẫn giải bài tập dùng hàm INDEX kết hợp hàm MATCH có file mẫu

Bạn có biết hàm INDEX và MATCH là những hàm tìm kiếm rất hiệu quả và hữu ích trong Excel? Để hiểu rõ hơn cách sử dụng hai hàm này, Gitiho sẽ hướng dẫn các bạn cách sử dụng hàm INDEX MATCH qua một số bài tập ứng dụng. Các bạn có thể tải file bài tập này ở cuối bài viết để thực hành với chúng mình nhé.

Xem thêm: Giới thiệu các hàm trong Excel và các ví dụ minh họa dễ hiểu

Các bài tập về hàm INDEX và MATCH cơ bản trong Excel

Hàm INDEX và MATCH: Dùng hàm MATCH tìm dữ liệu trong Excel

Trong phần này, chúng ta sẽ cùng tìm hiểu cách sử dụng các hàm tìm kiếm thông qua các bài tập về hàm INDEX và MATCH trong Excel nhé.

Ví dụ chúng ta có bảng tính sau:

Trong bài tập hàm INDEX và MATCH này, chúng ta được cung cấp một bảng chứa đầy đủ thông tin về họ tên, số điện thoại, Email, khu vực làm việc của 6 nhân viên. Nếu muốn biết thông tin của nhân viên “Lê Thị Thủy” thì bạn phải 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. Hãy cùng tìm hiểu cách sử dụng hàm INDEX và MATCH để giải bài tập này nhé.

Đầu tiên, bạn có thể sử dụng hàm MATCH với cú pháp:

=MATCH(lookup_value, lookup_array, [match_type])

Trong đó:

• lookup_value: giá trị làm căn cứ tìm kiếm (tìm theo cái gì); ở đây là tên Lê Thị Thủy.
• lookup_array: là nơi dò tìm (bạn sẽ tìm ở đâu); ở đây là cột Họ tên.
• match_type: (Tham số này không bắt buộc): phương pháp tìm kiếm (tìm đúng theo giá trị hay tìm theo khoảng giá trị); ở đây tìm chính xác theo tên.

Do đó chúng ta có thể viết hàm MATCH như sau:

=MATCH(Tên cần tìm, cột Họ tên, tìm chính xác theo tên)

• 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

Nếu chỉ cho kết quả là số 3 thì bạn chưa thấy có nhiều ý nghĩa phải không? Hãy tiếp tục tìm hiểu lợi ích của hàm MATCH khi kết hợp với hàm INDEX tạo thành một cặp hàm INDEX MATCH bất bại trong Excel nhé.

Hàm INDEX và MATCH: Dùng hàm INDEX MATCH để tìm dữ liệu trong Excel

Khi bạn đã có vị trí là dòng thứ 3 trong bảng, bạn có thể lấy được bất kỳ thông tin nào ở dòng này nếu xác định thêm được cột chứa nội dung cần tìm. Khi đó bạn có thể kết hợp hàm INDEX và MATCH theo cách:

• Hàm INDEX chỉ định cột chứa kết quả cần tìm.
• Hàm MATCH chỉ định dòng cần tìm.

Khi đó giao điểm giữa cột và dòng này chính là vị trí kết quả cần tìm.

Chúng ta cùng tìm hiểu một bài tập về hàm INDEX và MATCH trong Excel với yêu cầu: Tìm Email của nhân viên Lê Thị Thủy.

Để giải bài toán hàm INDEX và MATCH trong trường hợp này, chúng ta sẽ sử dụng công thức kết hợp hàm INDEX và MATCH như sau tại ô D15 trong trang tính:

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

Công thức hàm INDEX MATCH lúc này được giải thích như sau:

• 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 trong ví dụ trước.

Kết quả đúng của bài tập về hàm INDEX và MATCH là giao điểm của cột E với dòng 3, cho ra vị trí ô E3 là ô chứa giá trị cần tìm.

Tham khảo: Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất

Bài tập tìm kiếm từ phải qua trái với hàm INDEX và MATCH

Thông thường để tìm kiếm trong Excel chúng ta thường dùng hàm VLOOKUP. Nhưng hàm này có nhược điểm là không thể tìm được theo chiều từ phải qua trái. Nhưng bạn có thể thực hiện điều này rất dễ dàng với hàm INDEX và MATCH.

Để tìm hiểu cách làm, chúng ta sẽ tiến hành giải bài tập về hàm INDEX và MATCH trong Excel như sau: Tìm tên dựa vào thông tin Email, biết cột Email ở bên phải cột Họ tên.

Cách giải bài tập hàm INDEX và MATCH trên như sau:

• Viết hàm MATCH để tìm vị trí dòng chứa thông tin Email.

=MATCH(tên Email, vùng chứa Email, 0)

• Kết hợp hàm INDEX MATCH 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))

Như vậy về cách viết hàm không có gì thay đổi, nhưng phương pháp tìm kiếm có thể cho phép tìm từ phải qua trái, từ dưới lên trên một cách dễ dàng bằng cách sử dụng hàm INDEX và MATCH thay vì hàm VLOOKUP hay HLOOKUP.

Tham khảo: Hướng dẫn cách dùng hàm HLOOKUP, so sánh HLOOKUP với INDEX MATCH

Bài tập tìm đơn giá theo nhiều điều kiện với hàm INDEX và MATCH

Trong trường hợp giá trị tìm kiếm của bạn thay đổi trên cả dòng và cột trong 1 bảng dữ liệu, bạn vẫn có thể sử dụng hàm INDEX MATCH để tìm kiếm được. Hãy xem ví dụ bài tập về hàm INDEX và MATCH trong Excel trong trường hợp cần tìm kiếm đơn giá của 1 sản phẩm khi thay đổi cả về mã sản phẩm và mã công ty:

Trong ví dụ này, chúng ta có Mã hàng và Mã công ty là các điều kiện cần tìm. Mã này có thể thay đổi:

• Mã hàng thay đổi trong vùng B3:E3
• Mã công ty có thể thay đổi trong vùng A4:A7

Với bất kỳ mã nào, bạn cũng phải xác định được đơn giá phù hợp.

Các bước thực hiện để giải bài tập hàm INDEX và 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

Thay vì sử dụng công thức MATCH INDEX, bạn chỉ cần dùng hàm MATCH để tìm Mã hàng (ô H2) trong vùng chứa tên mã hàng (A3:E3) và xem mã hàng cần tìm nằm ở cột thứ mấy trong bảng.

=MATCH(H2,A3:E3,0)

Chúng ta thấy hàm MATCH cho kết quả là số 2, tương ứng mã hàng này ở cột thứ 2 trong bảng.

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

Bạn sẽ dùng hàm MATCH để tìm Mã công ty (ô H3) trong vùng chứa tên công ty (A3:A7) để xem mã công ty cần tìm nằm ở dòng thứ mấy trong bảng:

=MATCH(H3,A3:A7,0)

Kết quả hàm MATCH trong trường hợp này cho là số 3, tương ứng với vị trí dòng thứ 3 trong bảng.

Như vậy bạn đã có cả 2 vị trí: số dòng và số cột trong bảng đơn giá, chỉ cần xác định giao điểm của dòng và cột này là có thể cho ra kết quả đơn giá cần tìm. Để làm việc này chúng ta sẽ sử dụng hàm INDEX MATCH.

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

Khi viết hàm INDEX, bạn cần chỉ ra 3 vị trí:

• Bảng cần tìm: bảng A3:E7.
• Dòng cần tìm: hàm MATCH tìm theo mã công ty.
• Cột cần tìm: hàm MATCH tìm theo mã hàng.

Khi đó chúng ta có hàm INDEX như sau:

=INDEX(A3:E7,I3,I2)

Trong đó:

• Ô I3 chứa kết quả hàm INDEX và MATCH tìm theo mã công ty.
• Ô I2 chứa kết quả hàm INDEX và MATCH tìm theo mã hàng.

Xem thêm: Hướng dẫn dùng hàm INDEX dạng mảng để tham chiếu nhiều kết quả cùng lúc trên Excel

Nếu viết 1 cách tổng quát, chúng ta có công thức như sau để giải bài tập hàm INDEX và MATCH:

=INDEX(A3:E7, MATCH(H3,A3:A7,0), MATCH(H2,A3:E3,0))

Trong trường hợp này chúng ta viết hàm INDEX gồm 3 tham số, khác với 2 ví dụ trước chỉ có 2 tham số. Bởi vì:

• Khi vùng tìm kiếm của hàm INDEX chỉ có duy nhất 1 cột thì bạn không cần viết tham số thứ 3 (là cột cần tìm).
• Khi vùng tìm kiếm của hàm INDEX có nhiều hơn 1 cột thì bạn bắt buộc phải viết tham số thứ 3 để chỉ có tìm kết quả ở cột nào.

Kết luận

Như vậy 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 thông qua 4 ví dụ tiêu biểu rồi. Một số kết luận có thể rút ra là:

• Hàm MATCH dùng để tìm ra vị trí số dòng, số cột
• Hàm INDEX có 2 cách viết: Viết rút gọn nếu chỉ xác định trên 1 cột (hoặc 1 hàng), viết đầy đủ nếu tìm trên 1 vùng gồm nhiều dòng, nhiều cột. Khi viết đầy đủ thì cần tới 2 hàm MATCH
• Hàm INDEX MATCH thay thế được cho hàm VLOOKUP, HLOOKUP trong mọi trường hợp tìm kiếm, và phương pháp tìm kiếm không bị hạn chế.

Hy vọng bài viết này sẽ hữu ích trong việc nâng cao khả năng viết công thức tính trong Excel của bạn. Nếu có bất kỳ thắc mắc gì bạn có thể góp ý, phàn hồi ngay dưới bài viết này để chúng tôi có thể giải quyết mọi thắc mắc của bạn một cách sớm nhất. Để tích lũy thêm kiến thức về chủ đề Excel, các bạn đừng quên đón đọc các bài viết trên blog Gitiho nhé. Bên cạnh đó, hãy đăng ký khóa học Tuyệt đỉnh Excel để cùng chúng mình làm chủ phần mềm này thôi!

Gitiho chúc các bạn áp dụng thành công thủ thuật với hàm INDEX và MATCH!