Cách sử dụng hàm Index, Match qua bài tập hàm kèm lời giải
Hàm Index và Match được biết đến là hàm đặc biệt được tích hợp ở Excel. Đây là hàm đặc biệt có thể thay thế cho hàm Vlookup. Thế nhưng để có thể nắm rõ chính xác từng chút một cách sử dụng và ứng dụng của 2 hàm không phải ai cũng biết. Vậy để linh hoạt hơn khi dùng bạn hãy cùng luyện tập qua bài tập hàm Index và Match kèm lời giải cơ bản.
Tìm hiểu về hàm Index và Match trong Excel
Để bước vào các bài tập hàm Index và Match bạn hãy điểm qua một số thông tin cơ bản về 2 hàm. Điều này sẽ giúp bạn ôn lại được kiến thức và dễ dàng thực hiện hơn khi sử dụng. Cụ thể:
Đối với hàm Index
Hàm Index có nhiệm vụ chính là trả về giá trị ở ô vị trí cột, hàng tham chiếu trong mảng. Bao gồm các hàng và các cột. Công thức chung của hàm là:
-index(array, row_num,[columm_num])
Trong đó:
- Array: mảng được tham chiếu. Là một bảng gồm hàng, cột chứa giá trị cần tham chiếu. Lấy địa chỉ tuyệt đối khi muốn sao chép công thức.
- Row_num: Vị trí hàng ô cần tham chiếu giá trị ở bảng tham chiếu
- Column_num: Vị trí cột ô cần tham chiếu giá trị ở bảng tham chiếu
Đối với hàm Match
Công dung chính là trả về vị trí ô tham chiếu trong danh sách mảng. Bao gồm hàng hoặc cột. Công thức chính của hàm là:
=match(lookup_value; lookup_array, match type)
Trong đó:
Lookup_value: giá trị tìm kiếm. Giá trị mang ra dò tìm vị trí.
Lookup_array: mảng chứa giá trị cần dò tìm. Đó có thể là hàng hoặc cột. Lấy địa chỉ tuyệt đối lúc sao chép công thức.
Match Type: Kiểu tham chiếu. Có 3 tùy chọn khác nhau là:
- Match type là 0: dò tìm chính xác từng giá trị
- Match type 1: Tìm tới giá trị lớn hơn giá trị ở mảng tìm kiếm
- Match type -1: Tìm giá trị nhỏ hơn giá trị ở mảng tìm kiếm
Có thể nói một khi kết hợp Index và Match lại với nhau thì còn tốt hơn sử dụng Vlookup rất nhiều. Vì đây là 2 hàm sẽ giúp bạn làm việc hiệu nhanh hơn. Tính tùy biến khi sử dụng 2 hàm cũng có thể ứng dụng cho nhiều trường hợp khác nhau. Thậm chí so với Vlookup thì những lỗi xảy ra sẽ ít hơn hẳn.
Các bài tập hàm Index và Match và các thực hiện
Về cơ bản các bài tập Index và Match có thể thực hiện theo nhiều phương thức khác nhau. Tùy theo nhu cầu người dùng mà các tùy biến sẽ khác nhau. Tuy nhiên có 3 bài tập cơ bản thường được áp dụng nhất đó là:
Bài tập 1: Sử dụng hàm Match tìm vị trí theo dòng, cột
Đối với bài tập này sẽ cho gợi ý bảng tính như sau:
| Stt | 1 | 2 | 3 | 4 |
| 1 | Họ tên | Số điện thoại | Khu vực | |
| 2 | Trần Văn Long | 097.123.xxx
|
long@abc.vn | Hà Nội |
| 3 | Lê thị Thủy | 0163.255.xxx | thuy@abc.vn | Đà Nẵng |
| 4 | Nguyễn Đức Trung | 091.452.xxx
|
trung@abc.vn | Hà Nội |
| 5 | Ngô Mạnh Hùng | 098.465.xxx | Hùng@abc.vn | Hà Nội |
| 6 | Đào Thị Huệ | 0123.781.xxx | hue@abc.vn | Đà Nẵng |
Vậy ví dụ bây giờ bạn tìm thông tin của người có tên “Lê thị Thủy” thì làm thế nào? Công việc đầu tiên là xác định xem người ấy nằm tại vị trí thứ mấy trong bảng tính. Để thực hiện điều này bạn cần dùng đến hàm Match là sẽ biết được người đó nằm ở dòng máy trong bảng. Cụ thể:
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 mà bạn cần tìm. Cột Họ tên là vùng C3:C8. Một khi nhập xong bạn sẽ thấy kết quả hiển thị như hình ảnh.
Như vậy Match cho kết quả số 3. Tương ứng với đó là dòng 3 trong bảng dữ liệu. Vậy tìm ra số 3 có ý nghĩa gì? Bạn có thể xem thêm bài tập 2.
Bài tập 2: Kết hợp hàm Index với Match tìm Email theo tên
Bài tập Index và Match tiếp theo là kết hợp 2 hàm tìm Email theo tên. Cũng lấy ví dụ từ bảng trên, bạn thấy tại dòng 3 ở cột Email xuất hiện Email cần tìm. Như vậy có thể hiểu là chỉ cần đóng cột Email tại vị trí thứ 3 ở bảng là có kết quả. Đây chính là cách kết hợp giữa hàm Index và Match.
Ở công thức ô D15 tìm Email cho thấy công thức cụ thể là
=Index(E3:E8,Match(D12,C3:C8,0)
Công thức này hàm Index đóng vai trò:
- Chỉ cột chứa giá trị cần tìm. Đó là cột E, vùng E3:E8
- Chỉ dòng chứa giá trị cần tìm. Dòng này đã được xác định bởi hàm Match.
Giao điểm giữa dòng và cột ở hàm Index chính là thành quả Email cần tìm.
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 khi dò tìm đa phần người dùng thực hiện từ Trái qua Phải. Thế nếu muốn dò từ Phải qua Trái thì làm sao. Đây là cách bạn có thể áp dụng bài tập kết hợp Index và Match hoàn hảo. Ví dụ sau bạn có thể tham khảo là tìm tên dựa vào Email.
Theo như bảng gợi ý thì cột Email nằm ở cột thứ 3. Còn riêng cột Họ tên sẽ là cột thứ 1. Như vậy để tìm mục Họ tên qua Email nghĩa là bạn phải tìm từ Phải qua Trái. Quá trình thực hiện bạn làm như sau:
Đầu tiên bạn hãy dùng hàm Match để tiến hành tìm vị trí dòng Email ở đâu. Bạn có thể làm tương tự như gợi ý bài tập 1. Đó là áp dụng công thức =Match(Email,vùng chứa Email,0). Sau khi tìm được dòng Email bạn hãy kết hợp hàm Index để tìm vị trí cột có kết quả cần tìm. Đó là (cột họ tên,vùng C3:C8) và công thức được áp dụng =Index(C3:C8,Match(D12,E3:E8,0)). Vậy là bạn đã tìm được dòng Họ tên cần tìm rồi rất đơn giản phải không?
Lời kết
Vậy vừa rồi là 3 bài tập hàm Index và Match ví dụ và cách giải đáp cụ thể. Hy vọng thông qua ví dụ bạn sẽ hình dung được phương pháp dùng hàm Index và Match để dò tìm, tham chiếu hay tìm kiếm ở Excel. Một khi ứng dụng tốt các bài tập gợi ý như trên chắc chắn bạn sẽ thực hiện được những yêu cầu phức tạp hơn.