Hướng dẫn giải bài tập Excel với hàm INDEX và hàm MATCH chi tiết

Trong Excel, hàm INDEX kết hợp hàm MATCH để tìm kiếm các kết quả ở hàng và cột, có chức năng thay thế hàm VLOOKUP và hàm HLOOKUP bởi vì kết quả được trả về từ sự kết hợp INDEX và MATCH mang tới kết quả tốt hơn hẳn 2 hàm trên. Để hiểu rõ hơn về hàm INDEX và hàm MATCH kết hợp trong Excel hãy cùng Gitiho giải các bài tập cụ thể dưới đây nhé!

Xem thêm: Hướng dẫn cách viết các hàm trong Excel chi tiết, dễ hiểu nhất
 

Giải bài tập Excel về hàm INDEX và hàm MATCH

Bài tập 1: Tìm vị trí theo dòng, cột bằng hàm MATCH trong Excel

Cú pháp hàm MATCH:

=MATCH(lookup_value; lookup_array; [match_type])

Trong đó:

- lookup_value (bắt buộc): Giá trị bạn muốn tìm vị trí tương đối

- lookup_array (bắt buộc): Vùng tìm kiếm dữ liệu

- match_type: Là 1 trong 3 số sau

+ Số 1 hoặc bỏ trống: Hàm MATCH sẽ tìm giá trị lớn nhất trong vùng tìm kiếm nhưng nhỏ nhất hoặc bằng giá trị đang được tìm kiếm.

+ Số 0: Tìm kiếm chính xác giá trị trong vùng tìm kiếm và trả về giá trị đầu tiên mà nó nhìn thấy

+ Số -1: Hàm MATCH sẽ tìm giá trị nhỏ nhất trong vùng tìm kiếm nhưng lớn nhất hoặc bằng giá trị đang được tìm kiếm.

Cho bảng dữ liệu sau:

Để biết được thông tin của nhân viên Lê Văn Kiên, trước hết bạn phải tìm xem nhân viên này thuộc dòng thứ mấy trong bảng tính. Để tìm kiếm vị trí dòng của nhân viên, ta sử dụng hàm MATCH.

Cú pháp hàm MATCH trong trường hợp này là:

=MATCH("Lê Văn Kiên"; cột "Họ và tên"; 0)

=MATCH(F2;A1:A8;0)

Kết quả: Hàm MATCH trả về giá trị 5 tương ứng với dòng thứ 5 trong bảng dữ liệu. 

Như vậy, bây giờ bạn đã tìm được đầy đủ thông tin của nhân viên Lê Văn Kiên có trong bảng dữ liệu.

Xem thêm: Hướng dẫn cách giải bài tập Excel với hàm IF và hàm RANK

Bài tập 2: Tìm email theo tên bằng hàm INDEX kết hợp với hàm MATCH trong Excel

Hàm INDEX trong Excel được sử dụng nhằm mục đích trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. Hàm INDEX được sử dụng khi bạn biết hoặc tính toán vị trí của một phần tử trong bảng tính Excel và bạn muốn nhận giá trị thực của phần tử đó.

Cú pháp hàm INDEX cơ bản:

=INDEX(array; row_num; [column_num])

Trong đó:

- array: Vùng ô hoặc một hằng số mảng nào đó

- row_num: Chọn hàng trong mảng từ đó trả về một giá trị

- column: Chọn cột trong mảng từ đó trả về một giá trị

Cú pháp hàm INDEX nâng cao:

=INDEX(reference; row_num;[column_num];[area_num])

Trong đó:

- reference: Vùng tham chiếu (có thể kết hơp nhiều mảng với nhau);

- row_num: Chỉ số hàng từ đó trả về một tham chiếu;

- column_num: Chỉ số cột từ đó trả về một tham chiếu;

- area_num: Số của vùng ô sẽ trả về giá trị trong reference (tức là số thứ tự của mảng trong vùng tham chiếu). Nếu area_num được bỏ qua thì hàm INDEX mặc định dùng vùng 1.

Trở lại bài tập Excel trên, nếu chúng ta dóng cột Email với dòng thứ 5 ta sẽ có được Email của nhân viên Lê Văn Kiên. Để tra cứu thông tin tương ứng cần thiết nhanh hơn chúng ta sử dụng hàm INDEX kết hợp với hàm MATCH.

Cú pháp tìm kiếm Email của nhân viên Lê Văn Kiên trong trường hợp này là:

=INDEX(C2:C8;MATCH(F2;A2:A8;0))

Trong đó:

C2:C8: Cột Email

F2: Giá trị tìm kiếm

A2:A8: Cột Họ và tên

Như vậy cú pháp INDEX kết hợp với MATCH có ý nghĩa:

- Chỉ ra cột chứa giá trị cần tìm

- Chỉ ra dòng chứa giá trị cần tìm

- Giao điểm giữa dòng và cột chính là giá trị cần tìm.

Xem thêm: Bài tập Excel từ cơ bản tới nâng cao có lời giải

Bài tập 3: Hàm INDEX kết hợp hàm MATCH để dò tìm từ phải qua trái trong Excel

Ví dụ: Tìm tên nhân viên dựa vào Email của họ

Thông thường, chúng ta thường tìm kiếm kết quả theo chiều từ trái sang phải, tuy nhiên trong trường hợp này, cột Email nằm ở cột C còn cột Họ và tên nằm ở cột A, vì vậy nếu muốn tìm kiếm tên của nhân viên dựa vào Email, bạn phải dò tìm từ phải qua trái.

Các bước tìm kiếm từ phải qua trái như sau:

- Dùng hàm MATCH để tìm vị trí dòng Email trong cột Email:

= MATCH(Email; cột chứa Email; 0)

- Kết hợp với hàm INDEX để chỉ ra tên cần tim trong cột Họ và tên, cột A

= INDEX(A2:A8;MATCH(G3;C2:C8;0))

Xem thêm: BÀI TẬP EXCEL VỚI CÁCH SỬ DỤNG HÀM VLOOKUP VÀ ỨNG DỤNG NÂNG CAO THƯỜNG GẶP (PHẦN 1)

Bài tập 4: Xác định đơn giá theo điều kiện của một mặt hàng bằng hàm INDEX kết hợp hàm MATCH trong Excel

Cho bảng tính sau, tìm giá của một mặt hàng "BA" trong Công ty C

Bạn làm theo các bước sau để tìm đơn giá của mặt hàng BA trong công ty C nhé!

Bước 1: Xác định vị trí mặt hàng thuộc cột thứ bao nhiêu trong bảng 

=MATCH(F11;A10:D10;0)
 

Kết quả trả về là 2, tương ứng với mặt hàng này nằm ở cột thứ 2

Bước 2: Xác định vị trí công ty thuộc dòng thứ mấy 

CÚ pháp hàm:

=MATCH(G11;A10:A14;0)

- Cú pháp dùng để tham chiếu Công ty trong cột chứa tên công ty - cột A

- Kết quả trả về là 4 có nghĩa là thông tin của công ty C nằm ở dòng thứ 4.

Bước 3: Kết hợp hàm INDEX để tìm đơn giá của sản phẩm

Giao điểm giữa cột thứ 2 và dòng thứ 3 chính là kết quả đơn giá cần tìm. Bây giờ ta dùng hàm INDEX để tìm ra giao điểm đó.

Cú pháp hàm INDEX:

=INDEX($A$10:$D$14;G12;F12)

Trong đó: 

$A$10:$D$14: Vùng tham chiếu tìm kiếm

G12: Vị trí dòng được xác định bởi hàm MATCH 

F12: Vị trí cột được xác định bởi hàm MATCH

Như vậy bạn đã nhận được đơn giá của sản phẩm cần tìm nhanh chóng mà chính xác.

Xem thêm: Cách tính đơn giá trong Excel bằng hàm VLOOKUP chuẩn

Kết luận

Qua bài viết trên, Gitiho đã cùng bạn tìm hiểu về hàm INDEX và hàm MATCH qua những bài tập Excel cụ thể có lời giải chi tiết. Hy vọng bạn sẽ thực hiện thành công cho công việc của mình và đừng quên theo dõi chúng mình để xem thêm các bài viết bổ ích khác nhé!

Ngay bây giờ hãy cùng luyện tập thêm với bộ bài tập Excel có đáp án của Gitiho để nằm lòng tin học văn phòng nhé! 

Để học thêm về các hàm trong Excel bạn hãy đăng ký khóa học Thành thạo HÀM trong Excel cho công việc. Học xong khóa học bạn sẽ hiểu cách thức hoạt động của 75+ hàm trong Excel hiểu biết sâu sắc về các công thức và hàm nâng cao của Excel giúp biến Excel từ một chương trình bảng tính cơ bản thành một công cụ phân tích năng động và mạnh mẽ. Để tham gia khóa học bạn hãy đăng ký tại đây.Khóa học này sẽ cung cấp cho bạn sự hiểu biết sâu sắc về các cô