Tìm hiểu một số bài tập hàm Index và Match

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ể:

Tìm hiểu một số bài tập hàm Index và Match
Tìm hiểu một số bài tập hàm Index và Match

Đố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 Email 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ập theo công thức hàm Match để tìm tên người dùng ở cột trong Excel
Nhập theo công thức hàm Match để tìm tên người dùng ở cột trong Excel

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.

Gợi ý cách kết hợp giữa hàm Index và Match hoàn hảo
Gợi ý cách kết hợp giữa hàm Index và Match hoàn hảo

Ở 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.

Bảng ví dụ để thực hiện bài tập kết hợp hàm Index và Match trong Excel
Bảng ví dụ để thực hiện bài tập kết hợp hàm Index và Match trong Excel

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?

Hoàn thành cách tìm cột từ phải qua trái
Hoàn thành cách tìm cột từ phải qua trái

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.

Leave a Reply

Your email address will not be published. Required fields are marked *