KẾT HỢP HÀM INDEX VÀ HÀM MATCH TRONG EXCEL

I – Giải thích hàm

1. Hàm INDEX

– Ý nghĩa: Hàm INDEX trả về một giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc phạm vi.

– Cú pháp:

INDEX(array, row_num, [column_num])

Trong đó:

+ array: Bắt buộc. Một phạm vi ô hoặc một hằng số mảng.

+ row_num: Bắt buộc, trừ khi column_num có mặt. Chọn hàng trong mảng mà từ đó trả về một giá trị. Nếu row_num được bỏ qua, column_num được yêu cầu.

+ column_num :  Tùy chọn. Chọn cột trong mảng mà từ đó trả về một giá trị. Nếu column_num được bỏ qua, row_num được yêu cầu.

2. Hàm MATCH

– Ý nghĩa: Hàm MATCH tìm một mục được chỉ định trong phạm vi của ô, sau đó trả về vị trí tương đối của mục đó trong phạm vi này. Ví dụ, nếu phạm vi A1:A3 chứa giá trị 5, 25 và 38 thì công thức =MATCH(25,A1:A3,0) sẽ trả về số 2, vì 25 là mục thứ hai trong phạm vi.

– Cú pháp:

MATCH (lookup_value, lookup_array, [match_type])

Trong đó:

+ lookup_value: là giá trị bạn muốn tìm vị trí tương đối trong mảng, có thể thuộc loại dữ liệu số, text, logic hoặc là 1 tham chiếu đến 1 địa chỉ ô

+ lookup_array:  (tham số bắt buộc) vùng muốn tìm giá trị

+ [match_type]: Tùy chọn. Số -1, 0 hoặc 1. Đối số kiểu khớp chỉ rõ cách Excel so khớp giá trị tìm kiếm với các giá trị trong mảng tìm kiếm. Giá trị mặc định cho đối số này là 1.

(- Nếu bạn điền 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ỏ hơn hoặc bằng giá trị đang được tìm kiếm. Chú ý: vùng tìm kiếm cần được sắp xếp theo chiều tăng dần, nghĩa là, từ giá trị nhỏ nhất đến giá trị lớn nhất hoặc từ A tới Z

– Nếu bạn điền số 0: hàm Match sẽ tìm chính xác giá trị cần tìm trong vùng và trả lại vị trí đầu tiên mà nó tìm thấy

– Nếu bạn điền số -1: hàm Match sẽ tìm giá trị nhỏ nhất trong vùng tìm kiếm nhưng lơn hơn hoặc bằng giá trị đang được tìm kiếm. Chú ý: vùng tìm kiếm cần được sắp xếp theo thứ tự giảm dần, nghĩa là, từ giá trị lớn nhất đến giá trị nhỏ nhất hoặc từ Z tới A)

II- Bài tập

Cho bảng dữ liệu bên trái, điền công thức vào bảng kết quả bên phải sao cho thỏa mãn yêu cầu:
Khi chọn sản phẩm bất kỳ, Nhóm sản phẩm và Lợi nhuận sẽ trả về kết quả tương ứng.

Sử dụng: Data validation, các hàm Index và Match

Kết quả cần đạt:

Hướng dẫn:

Bước 1: Tạo Data validation cho mục Sản phẩm – ô H5

Di chuột tới ô H5 > Chọn Data > Chọn Data Validation > Data Validation…

Tại cửa số Data Validation, phần Allow chọn List, phần Source quét vùng từ cột sản phẩm ở phần Dữ liệu (ô B6:B42)

Ta được

Bước 2: Từ Sản phẩm (ô H5) tham chiếu tìm ra Nhóm sản phẩm (ô H6).

Cột tham chiếu tìm ra Nhóm sản phẩm chính là cột A. Tuy nhiên cột A này chứa nhiều giá trị lặp lại, nên vậy không thể dùng hàm Vlookup để tham chiếu giá trị.

Đối với trường hợp tìm kiếm đặc biệt này, ta sẽ dùng hàm Index và Match để thay thế cho hàm vlookup.

Tại ô H6, điền công thức:

=INDEX(A6:A42,MATCH(H5,B6:B42,0),0)

Giải thích, bóc tách công thức:

+ A6:A42 : Đây là cột chứa kết quả cần trả về

+ MATCH(H5,B6:B42,0) : Hàm này trả về vị trí của giá trị trong ô H5 trong vùng tìm kiếm B6:B42. Ví dụ: trong vùng B6:B42 thì sản phẩm Kem lót – Makeup prmer ( thuộc ô H5)  đang có vị trí dòng số 1.

+ Ý nghĩa cả hàm: Hàm Index sẽ tìm giá trị dòng số n= MATCH(H5,B6:B42,0) , từ dòng số n, tìm trong cột A6:A42 giá trị tương ứng và trả về giá trị này!

Bước 3: Từ ô Nhóm sản phẩm (H6) tìm ra Lợi nhuận tương ứng (ô H7)

Tại ô H7, điền công thức:

=INDEX(D6:D42,MATCH(H5,B6:B42,0))

Kết quả:

TRÂN TRỌNG CẢM ƠN!

0 Points


Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *