Hàm INDEX MATCH là hàm dò tìm kiếm rất thông dụng hiện nay, hay còn được gọi là hàm VLOOKUP, HLOOKUP ngược, đây là hàm giúp người dùng tìm kiếm giá trị với nhiều điều kiện nhanh nhất có thể. Bài viết dưới đây Azgad Edu sẽ hướng dẫn bạn thật chi tiết về cách dùng của hàm INDEX MATCH trong Excel và những điểm lưu ý khi dùng hàm Excel này nhé!
Toc
Hàm INDEX và hàm MATCH
Để sử dụng kết hợp hàm INDEX MATCH trong Excel, bạn cần hiểu rõ cấu trúc cũng như cách sử dụng của hàm Excel INDEX và MATCH này nhé.
Hàm INDEX cơ bản
= INDEX ( Mảng , Row_number, [Column_number])
Trong đó:
- Array: Mảng chứa giá trị được trả về.
- Row_number: Vị trí hàng trong mảng chứa giá trị trả về. Row_number bắt buộc phải có. Nếu bạn khai báo khai báo Column_number thì có thể bỏ qua bước này.
- Column_number: Vị trí cột trong mảng chứa giá trị trả về. Nếu bạn đã khai báo Row_number thì có thể bỏ qua bước này.
Hàm INDEX nâng cao
= INDEX (( Tham chiếu ), Row_number, [Column_number], [Area_number])
Trong đó:
- Reference: Phạm vi tham chiếu chứa nhiều mảng và các mảng sẽ phân cách với nhau bằng dấu phẩy.
- Row_number: Vị trí hàng trong mảng chứa giá trị trả về.
- Column_number: Vị trí cột trong mảng chứa giá trị trả về.
- Area_number: Thứ tự của mảng trong phạm vi tham chiếu. Nếu bỏ qua bước này thì hàm Excel mặc định sử dụng dữ liệu ở mảng 1.
Hàm MATCH trong Excel
= MATCH ( Lookup_value , Lookup_array , [Match_type])
Trong đó:
- Lookup_value: Giá trị được tìm kiếm trong mảng
- Lookup_array: Mảng hay phạm vi có chứa giá trị được tìm kiếm, có thể là số, văn bản, giá trị logic, bắt buộc phải có.
- Match_type: Kiểu tìm kiếm giá trị và không bắt buộc phải có.
- Match_type = 1 hoặc bỏ qua (Less than): Tìm kiếm giá trị lớn nhất mà giá trị đó nhỏ hơn hoặc bằng Lookup_value (Lookup_array phải được sắp xếp theo thứ tự tăng dần).
- Match_type = 0 (Exact Match): Hàm MATCH sẽ tìm kiếm giá trị thứ nhất bằng chính xác với Lookup_value (Lookup_value không cần sắp xếp theo thứ tự).
- Match_type = -1 (Greater than): Hàm MATCH tìm kiếm giá trị nhỏ nhất mà giá trị đó lớn hơn hoặc bằng Lookup_value (Lookup_array phải được sắp xếp theo thứ tự giảm dần).
Hàm INDEX MATCH trong Excel
Hàm INDEX và hàm MATCH khá đặc biệt trong Excel, với một số bài toán phức tạp, bạn có thể dùng hàm INDEX MATCH để thay thế cho hàm VLOOKUP hay HLOOKUP khi giải quyết công việc.
Ưu điểm của hàm INDEX MATCH là có thể dò tìm kiếm giá trị trên cả cột và dòng, khác với các hàm Excel còn lại là chỉ tìm được giá trị ở cột hoặc dòng. Hàm tìm kiếm trong Excel là dạng hàm cơ bản, khá thông dụng và có nhiều sự lựa chọn. Vì thế bạn nên cân nhắc yêu cầu của bài toán để lựa chọn sử dụng hàm Excel cho phù hợp.
Ngoài ra, hàm INDEX MATCH còn giúp bạn chèn hay xóa cột một cách an toàn vì hàm MATCH đã xác định được cột chứa giá trị phải tìm. Hàm tìm kiếm này không giới hạn kích thước cho dữ liệu được trả về, tốc độ xử lý cũng linh hoạt và nhanh hơn nhiều lần so với các hàm tìm kiếm Excel khác.
Kết hợp hàm INDEX MATCH để giải quyết bài toán
Hàm INDEX MATCH 1 điều kiện
Như bạn đã biết hàm VLOOKUP hay HLOOKUP chỉ trả về giá trị bên phải hoặc giá trị bên dưới so với giá trị dò tìm, ngược lại hàm sẽ báo lỗi. Tuy nhiên, hàm INDEX MATCH trong Excel có thể giải quyết được vấn đề này, bạn hãy thực hành cùng Azgad qua ví dụ dưới đây nhé.
Ví dụ 1: Hãy dùng hàm INDEX MATCH để xác định Tên xí nghiệp dựa vào Mã KH được cho ở hình dưới đây:
Để dùng hàm INDEX MATCH giải quyết bài toán bạn cần thực hiện lần lượt 2 bước sau:
Bước 1: Dùng hàm MATCH xác định vị trí của hàng tương ứng của từng Mặt hàng được cho ở bảng dưới.
Nhập công thức =MATCH(B12,$C$3:$C$8,0) vào ô địa chỉ
Trong đó:
- Lookup_value (B12): Giá trị được tìm kiếm trong mảng
- Lookup_array ($C$3:$C$8): Mảng chứa giá trị được tìm kiếm.
- Match_type (0): Kiểu tìm kiếm chính xác.
Sau đó nhấn ENTER bạn có thể thấy Mã KH XN1 có vị trí tương ứng là 1 trong mảng (C3:C8). Như vậy hàm Excel đã xác định thứ tự của Mã KH trong mảng được cho.
Bài viết liên quan 01:
1. https://azgad.vn/archive/1147/
2. https://azgad.vn/archive/1038/
3. https://azgad.vn/archive/1012/
Bước 2: Kết hợp hàm INDEX MATCH để trả về giá trị cho theo yêu cầu bài toán.
Nhập công thức hàm và ô địa chỉ
= INDEX ($ B $ 3: $ C $ 8, TRẬN ĐẤU ( B12 , $ C $ 3: $ C $ 8 , 0), 1)
Trong đó:
- $B$3:$C$8: Mảng chứa giá trị được trả về
- MATCH(B12,$C$3:$C$8,0): Vị trí hàng của giá trị được trả về
Vì hàm INDEX MATCH là công thức mảng nên để nhận được kết quả nhấn Ctrl + Shift + Enter.
Ví dụ hàm INDEX MATCH 1 điều kiện
Hàm INDEX MATCH nhiều điều kiện
Để hiểu rõ về cách dùng kết hợp của hàm INDEX MATCH và ứng dụng của nó trong giải quyết bài toán hay công việc, bạn hãy cùng Azgad thực hành ví dụ bên dưới nhé.
Ví dụ 2: Hãy dùng hàm INDEX MATCH để điền đầy đủ thông tin cho cột Chiết khấu dựa trên Mặt hàng và Trị giá được cho ở bảng mức chiết khấu (B12:E15) được cho bên dưới.
Ví dụ về hàm INDEX MATCH trong Excel
Theo yêu cầu của bài, ở đây hàm Excel phải tìm kiếm giá trị thỏa 2 điều kiện là Chiết khấu được lập phải dựa trên Mặt hàng và Trị giá, vì thế để thuận tiện bạn nên sử dụng hàm INDEX và MATCH nhé.
Để sử dụng hàm Excel INDEX MATCH giải quyết bài toán này, bạn cần thực hiện lần lượt các thao tác sau:
Bước 1: Dùng hàm MATCH xác định vị trí của hàng tương ứng của từng Mặt hàng được cho ở bảng dưới.
Nhập công thức =MATCH(D3,$B$12:$B$15,0) vào ô địa chỉ
Trong đó:
- Lookup_value (D3): Giá trị được tìm kiếm trong mảng
- Lookup_array ($B$12:$B$15): Mảng chứa giá trị được tìm kiếm.
- Match_type (0): Kiểu tìm kiếm chính xác.
Sau đó nhấn ENTER và COPY công thức cho các địa chỉ ô còn lại, bạn có thể thấy Mặt hàng AL có vị trí tương ứng là 3 trong mảng (B12:B15). Như vậy hàm Excel đã xác định thứ tự của mặt hàng trong mảng được cho.
Hàm MATCH tìm kiếm giá trị theo hàng trong Excel
Bài viết liên quan 02:
1. https://azgad.vn/archive/1053/
2. https://azgad.vn/archive/968/
3. https://azgad.vn/archive/1147/
Bước 2: Dùng hàm MATCH xác định vị trí của cột tương ứng của từng Trị giá được cho ở bảng dưới.
Nhập công thức =MATCH(G3,$B$12:$E$12,1)
Trong đó:
- Lookup_value (G3): Giá trị được tìm kiếm trong mảng
- Lookup_array ($B$12:$E$12): Mảng chứa giá trị được tìm kiếm.
- Match_type (1): Kiểu tìm kiếm nhỏ hơn.
Tiếp tục nhấn ENTER rồi COPY công thức cho địa chỉ các ô còn lại, bạn có thể thấy Trị giá 210 có vị trí tương ứng là 3 trong mảng (B12:E12).Kết quả hiển thị thứ tự được hàm Excel hiển thị như hình dưới đây:
Hàm MATCH tìm kiếm giá trị theo cột trong Excel
Bước 3: Kết hợp hàm INDEX MATCH để trả về giá trị cho cột Chiết khấu theo yêu cầu bài toán.
Nhập công thức hàm và ô địa chỉ
= INDEX ($ B $ 12: $ E $ 15, TRẬN ĐẤU ( D3 , $ B $ 12: $ B $ 15 , 0), TRẬN ĐẤU ( G3 , $ B $ 12: $ E $ 12 , 1))
Trong đó:
- $B$12:$E$15: Mảng chứa giá trị được trả về
- MATCH(D3,$B$12:$B$15,0): Vị trí hàng của giá trị được trả về
- MATCH(G3,$B$12:$E$12,1): VỊ trí cột của giá trị được trả về
Công thức hàm INDEX MATCH của bài toán
Sau đó nhấn CTRL + SHIFT + ENTER, kết quả hàm Excel trả về giá trị tương ứng của Mặt hàng AL (hàng 3) và Trị giá 210 (cột 3) là 7%.
Để nhận kết quả Chiết khấu đầy đủ từ hàm INDEX MATCH trả về, bạn thực hiện COPY công thức cho các ô địa chỉ còn lại.
Kết quả hàm INDEX MATCH của bài toán
Lưu ý khi dùng hàm INDEX MATCH trong tính toán
- Khi thiết lập công thức hàm INDEX và MATCH hãy nhớ dùng F4 để cố định giá trị để khi COPY công thức, địa chỉ sẽ không bị thay đổi.
- Hàm INDEX MATCH được thiết lập là công thức mảng nên khi bạn muốn kết quả được hiển thị cần nhấn Ctrl + Shift + Enter.
Qua bài viết trên, bạn đã có thể hình dung được cách sử dụng hàm INDEX MATCH để thực hiện các yêu cầu dò tìm kiếm giá trị với nhiều điều kiện và phức tạp trong Excel. Azgad hy vọng bạn sẽ giải quyết được mọi khó khăn của bài toán về vấn đề này, hãy theo dõi Azgad thường xuyên để có nhận thêm nhiều kiến thức hữu ích nhé!
>>> Xem thêm:Cách dùng hàm LEFT – Hàm cắt chuỗi ký tự bên trái trong ExcelCách dùng hàm RIGHT – Hàm cắt chuỗi ký tự bên phải trong Excel
Tác giả: Hoàng Thị Cẩm Tiên
