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, tuy nhiên do phải kết hợp 2 hàm này nên nhiều người thấy khó. Để 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, hàm 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.
Cách dùng hàm MATCH để dò tìm trong Excel
Ví dụ chúng ta có bảng tính sau:
Trong bảng này chúng ta có 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.
Khi đó bạn có thể sử dụng hàm MATCH như sau:
Cú pháp hàm MATCH gồm 3 phần:
- 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.
Hàm INDEX kết hợp với MATCH để tìm kiếm 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ới 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
Tìm Email của nhân viên Lê Thị Thủy
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ó tác dụng:
- 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ả 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.
Phương pháp tìm kiếm từ phải qua trái với hàm INDEX+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 kết hợp hàm MATCH. Hãy tìm hiểu ví dụ 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 làm 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 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))
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ài tập tìm đơn giá của mặt hàng theo nhiều điều kiện
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ụ về 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 như sau:
Bạn sẽ dùng hàm MATCH để tìm Mã hàng (ô H2) trong vùng chứa tên mã hàng (A3:E3) để xem mã hàng cần tìm nằm ở cột thứ mấy trong bảng
=MATCH(H2,A3:E3,0) thì bạn viết tham số thứ 3 (là cột cần tìm).
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ế.
Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của chúng tôi Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc... Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.
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ặpTải về file mẫu trong bài viết
Bạn có thể tải về file mẫu sử dụng trong bài viết tại địa chỉ bên dưới:
Tài liệu kèm theo bài viết
Với sứ mệnh: " ", đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án...
Gitiho tự hào khi được đồng hành cùng:
- trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,...