VinaOnline.net

Sử dụng hàm MATCH và hàm INDEX : Minh họa chi tiết

Sử dụng hàm MATCH và hàm INDEX thực chất là thay thế hàm VLOOKP/HLOOKUP và hàm IF. Hai hàm này kết hợp sẽ rất tiện lợi trong một số trường hợp tính toán. Vinaonline.net cùng bạn khảo sát chi tiết cách sử dụng hàm MATCH và hàm INDEX trong Excel.

I. Sử dụng hàm MATCH và hàm INDEX : MATCH

– Công dụng: Trả về vị trí cột hoặc vị trí hàng.

1. Cú pháp

MATCH(lookup_value, lookup_array, [match_type])

* lookup_value (bắt buộc) : Giá trị cần tìm kiếm kiếm (số, chuỗi, địa chỉ ô,…).

* lookup_array (bắt buộc) : Gồm một cột hoặc một hàng.

* [match_type] (không bắt buộc) : Có 3 tùy chọn.

1 hoặc bỏ đối số này : Hàm MATCH tìm giá trị lớn nhấtnhỏ hơn hoặc bằng giá trị cần tìm kiếm. Giá trị trong bảng tìm kiếm (lookup_array) phải đặt theo thứ tự TĂNG dần.

0 : Hàm MATCH tìm giá trị chính xác giá trị cần tìm kiếm. Giá trị trong bảng tìm kiếm không nhất thiết theo tứ tự.

-1 : Hàm MATCH tìm giá trị nhỏ nhấtlớn hoặc bằng giá trị cần tìm kiếm. Giá trị trong bảng tìm kiếm phải đặt theo thứ tự GIẢM dần.

Sử dụng hàm MATCH và hàm INDEX - Minh họa

Sử dụng hàm MATCH và hàm INDEX – Minh họa

2. Ví dụ

MATCH(“A”,B1:E1,0) = 1

MATCH(“A”,B1:E1,1) = 1

Tìm thấy “A” tại vị trí (cột) 1 trong bảng tìm kiếm.

MATCH(“E“,B1:E1,0) = #N/A

MATCH(“E“,B1:E1,1) = 4

Tìm giá trị lớn nhất gần với E

MATCH(“E“,B1:E1,-1) = #N/A

Chú ý:

Thông thường sử dụng đối số [match_type] là 0. Các tùy chọn khác rất mơ hồ, ít sử dụng trong thực tế.

II. Sử dụng hàm MATCH và hàm INDEX : INDEX

– Công dụng : Dò tìm trong bảng và trả về giá trị là giao của HÀNG và CỘT.

1. Cú pháp

INDEX(array, row_num, [column_num])

* array (bắt buộc) : Là một bảng gồm:

– Nhiều hàng, nhiều cột.

– Một hàng, nhiều cột.

– Nhiều hàng, một cột.

* row_num : Chỉ số hàng, từ 1, 2, …, n. Nếu array chỉ có một hàng, có thể bỏ qua đối số này.

* column_num : Chỉ số cột, từ 1, 2, …, n. Nếu array chỉ có một cột, có thể bỏ qua đối số này.

2. Ví dụ

INDEX(B1:E1,3) = C

array chỉ có một hàng (B1:E1), row_num không cần thiết. 3 là column_num.

INDEX(A1:A5,5) = T

array chỉ có một cột (A1:A5), 5 là row_num. column_num không cần thiết.

INDEX(A1:E5,2,4) = 500; hàng 2, cột 4

INDEX(A1:E5,4,2) = 40; hàng 4, cột 2

III. Sử dụng hàm MATCH và hàm INDEX : Áp dụng

Dựa vào bảng đã cho, hãy điền SỐ LƯỢNG

Sử dụng hàm MATCH và hàm INDEX - Minh họa

Sử dụng hàm MATCH và hàm INDEX – Áp dụng

Phân tích yêu cầu:

Đối với yêu cầu này, bạn có thể sử dụng hàm VLOOKUP / HLOOKUP kết hợp với hàm IF sẽ đáp ứng được. Vấn đề đặt ra, nếu có nhiều cột / hàng thì sao? Vì vậy tốt nhất là sử dụng hàm INDEX và kết hợp với hàm MATCH để lấy chỉ số hàng / cột trong hàm INDEX.

– Mảng / Bảng dò tìm (bảng giá trị – array) :

+ Trường hợp 1: chọn từ B2:E5, tại ô B2 (có giá trị 20) là hàng 1, cột 1.

+ Trường hợp 2: chọn từ A1:E5, tại ô B2 (có giá trị 20) là hàng 2, cột 2.

Tùy theo đối số array (bảng dò tìm) trong hàm INDEX mà sử dụng hàm MATCH cho hợp lý.

– Đối số hàng (row_num) : Là một cột (A2:A5 – Trường hợp 1), (A1:A5 – Trường hợp 2).

– Đối số cột (column_num) : Là một hàng (A2:E5 – Trường hợp 1), (A1:E5 – Trường hợp 2).

+ Trường hợp 1:

Trả về chỉ số hàng: MATCH(RIGTH(A8,1),$A$2:$A$5,0)

RIGHT(A8,1) để được 1 ký tự phải (V, G, H, T)

Trả về chỉ số cột: MATCH(LEFT(A8,1),$B$1:$E$1,0)

LEFT(A8,1) để được 1 ký tự trái (A, B, C, D)

+ Trường hợp 2: Tương tự (xem nội dung hàm INDEX)

Kết quả:

+ 1:

=INDEX($B$2:$E$5, MATCH(RIGHT(A8,1),$A$2:$A$5,0), MATCH(LEFT(A8,1),$B$1:$E$1,1))

+ 2:

=INDEX($A$1:$E$5, MATCH(RIGHT(A8,1),$A$1:$A$5,0), MATCH(LEFT(A8,1),$A$1:$E$1,1))

Kết luận

Sử dụng hàm MATCH và hàm INDEX ít gặp trong những bài toán thông thường. Nhưng rất hữu ích nếu gặp những yêu cầu tham chiếu dạng bảng. Bạn có thể tham khảo thêm các hàm dò tìm hoặc bài tập củng cố để rèn kỹ năng phân tích và giải quyết một bài toán Excel. Chúc bạn thành công.

Leave a Comment