Buzz
Hàm Index Match là sự kết hợp linh hoạt giữa INDEX và MATCH trong Excel, mang lại nhiều lợi ích quan trọng. Mặc dù là hai hàm khác nhau, nhưng khi sử dụng chung, chúng tạo ra cách hiệu quả để tìm kiếm dữ liệu. Đọc tiếp để hiểu chi tiết về cách sử dụng hàm này.
Hàm Index Match là gì?
Hàm Index Match là sự kết hợp thông minh giữa hai hàm INDEX và MATCH để thực hiện việc tìm kiếm dữ liệu một cách chính xác. Hãy tìm hiểu chi tiết về cách ứng dụng này:
Hàm Match
Hàm Match được sử dụng để xác định vị trí của một giá trị trong một phạm vi ô. Nó trả về vị trí tương đối của giá trị đó trong phạm vi. Công thức cú pháp của hàm là:
=MATCH(Giá_trị_tìm_kiếm,Dãy_Giá_trị,[Loại_tìm_kiếm]).
Trong đó:
- Giá_trị_tìm_kiếm: Là giá trị cần tìm kiếm trong Dãy_Giá_trị. Có thể là số, văn bản, giá trị logic hoặc tham chiếu đến một ô chứa dữ liệu.
- Dãy_Giá_trị: Là mảng hoặc phạm vi ô chứa dữ liệu được tìm kiếm.
- Loại_tìm_kiếm: Là loại tìm kiếm đang được thực hiện.
Hàm Index và hàm Match là bộ đôi hoàn hảo kết hợp trong Excel
Hàm Index
Hàm Index trả về giá trị của ô nằm trong một mảng, được xác định bởi số hàng và số cột. Công thức cú pháp:
=INDEX(Mảng,Hàng_số,[Cột_số])
Trong đó:
- Mảng: Là khu vực ô hoặc hàng số mảng đang được tìm kiếm.
- Hàng_số: Chọn hàng trong mảng để trả về giá trị.
- Cột_số: Chọn cột trong mảng để trả về giá trị.
Công thức hàm INDEX tham chiếu có dạng như sau:
=INDEX(Tham_chiếu,Hàng_số,[Cột_số],[Khu_vực_số])
Trong đó:
- Tham_chiếu: Là khu vực tham chiếu bắt buộc được xét đến.
- Hàng_số: Là chỉ số hàng tìm kiếm từ đó trả về một tham chiếu.
- Cột_số: Là chỉ số cột tìm kiếm từ đó trả về một tham chiếu.
- Khu_vực_số: Là số của vùng ô dữ liệu sẽ trả về giá trị trong Tham_chiếu. Nếu Khu_vực_số được bỏ qua thì hàm Index sẽ dùng vùng 1.
Cách kết hợp hàm Index và Match trong Excel
Để có thể sử dụng hàm Index Match trong Excel, bạn có thể thực hiện theo ví dụ sau để hiểu rõ hơn:
Ví dụ: Sử dụng hàm Index và hàm Match trong Excel để tìm địa chỉ theo tên trong bảng dữ liệu. Các bước làm:
Nhập ô tham chiếu công thức =INDEX(C3:C8,MATCH(C10,B3:B8,0))
Bước 2: Sau khi nhập công thức, nhấn Enter để hiển thị kết quả tính. Đơn giản như vậy, bạn đã sử dụng kết hợp 2 hàm để tìm kết quả dữ liệu.
Nhấn Enter để hiển thị kết quả cần tìm kiếm
Cách kết hợp hàm Index và Match nhiều điều kiện
Đối với trường hợp áp dụng hàm Index Match nhiều điều kiện, dưới đây là ví dụ minh họa cụ thể:
Ví dụ: Sử dụng hàm Index và hàm Match để tính đơn giá sản phẩm và hãng sản xuất. Trong công thức, hãy sử dụng $ để cố định giá trị các phạm vi. Giả sử theo đơn giá sản phẩm và hãng sản xuất trong bảng 2 (B15:E18), bạn cần điền đơn giá vào bảng 1 (B3:D12).
Ví dụ minh họa với dữ liệu bảng 1 và bảng 2
Dùng kết hợp 2 hàm Index và Match để dò tìm giá trị cho ô D4 như sau:
Bước 1: Sử dụng hàm Match để xác định vị trí hàng tương ứng với sản phẩm CDRom trong phạm vi B15:B18 của bảng 2, công thức:
=MATCH(B4,$B$15:$B$18,0). Kết quả là “4,” tương ứng với hàng số 4 trong bảng 2.
Nhập công thức hàm Match =MATCH(B4,$B$15:$B$18,0)
Bước 2: Tiếp theo, sử dụng hàm Match để xác định vị trí cột tương ứng với hãng sản xuất Samsung. Phạm vi xét đến là dữ liệu trong khoảng B15:E15. Công thức nhập vào là:
=MATCH(C4,$B$15:$E$15,0). Kết quả là giá trị “2,” tương ứng với cột số 2 trong bảng 2.
Nhập công thức hàm Match =MATCH(C4,$B$15:$E$15,0)
Bước 3: Tiếp theo, sử dụng hàm Index kết hợp với 2 hàm Match đã tính ở trên để tìm giá trị theo hàng và cột trong bảng 2. Công thức:
=INDEX($B$15:$E$18,MATCH(B4,$B$15:$B$18,0),MATCH(C4,$B$15:$E$15,0))
Nhập công thức kết hợp giữa hàm index và hàm match
Kết quả trả về giá trị tương ứng với hàng 4 (hàng Mouse) và cột 2 (Samsung) trong bảng 2 là 5.
Bước 4: Cuối cùng, copy công thức vừa tính cho các ô khác để hoàn thành bảng dữ liệu cần xét.
Copy công thức để hiển thị tất cả kết quả dữ liệu
Cách kết hợp hàm Index Match và Vlookup
Để kết hợp hàm Index Match Vlookup, sử dụng công thức như sau:
=VLOOKUP(INDEX(tham_chiếu;[hàng];[cột]);table_array;col_index_num;[range_lookup])
Trong đó:
- INDEX(tham_chiếu; [hàng]; [cột]): Giá trị sử dụng để dò tìm điều kiện.
- Table_array: Bảng chứa giá trị cần dò tìm dữ liệu.
- Col_index_num: Vị trí cột chứa giá trị muốn dò tìm.
- [range_lookup]: Phạm vi dữ liệu đang tìm kiếm. 0 là tìm tương đối, 1 là tìm tuyệt đối.
Ví dụ minh họa:
Dùng bảng dữ liệu để xác định điểm vùng của học sinh theo khu vực.
Bảng dữ liệu minh họa ví dụ
Sử dụng công thức:
=VLOOKUP(INDEX(A2:F9;1;6);$I$2:$J$9;2;0)
Trong đó:
- INDEX(A2:F9;1;6): Chọn giá trị từ khu vực ô A2:F9 ở hàng 1 cột 6.
- $I$2:$J$9: Dải ô từ I2:J9 chứa giá trị cần tìm kiếm.
- 2: Vị trí cột cho điểm vùng.
- 0: Kiểu tìm kiếm tương đối.
Bảng kết quả sau khi tính toán
Hướng dẫn sử dụng hàm Index Match kết hợp If
Để sử dụng kết hợp hàm Index Match và hàm If, bạn có thể sử dụng công thức sau:
=IF(INDEX(mảng_dữ_liệu;MATCH(khóa_tìm_kiếm,dải_ô,loại_tìm_kiếm);số_cột)+điều_kiện;giá_trị_nếu_đúng; giá_trị_nếu_sai)
Trong đó:
- Mảng_dữ_liệu: Là khu vực ô hoặc một hàng số mảng nào đó bắt buộc.
- Khóa_tìm_kiếm: Giá trị cần tìm kiếm dữ liệu.
- Dải_ô: Khu vực vùng dữ liệu bạn muốn xác định vị trí của khóa_tìm_kiếm. Chỉ có thể là một hàng hoặc một cột.
- Loại_tìm_kiếm: Phương thức tìm kiếm với 3 dạng chính: 0 cho kết quả khớp chính xác, 1 là giá trị mặc định, -1 là tìm kiếm dải ô được sắp xếp theo thứ tự giảm dần.
- Số_cột: Số cột của dữ liệu trả về.
- Giá_trị_nếu_đúng: Giá trị trả về nếu điều kiện đúng.
- Giá_trị_nếu_sai: Giá trị trả về nếu điều kiện sai.
Ví dụ minh họa:
Bảng dữ liệu để xác định sản phẩm bánh quy đã bán đạt KPI hay không. Để đạt KPI, số lượng bánh bán ra phải lớn hơn 10 sản phẩm.
Bảng minh họa ví dụ
Công thức áp dụng:
=IF(INDEX(A2:F5;MATCH('Bánh quy';A2:A5;0);3)>10;'Đạt';'Không đạt')
Trong đó: Hàm Match xác định địa chỉ hàng của ô chứa giá trị bánh quy. Hàm Index trả về số lượng bán của bánh quy. So sánh xem có lớn hơn 10 sản phẩm hay không. Kết quả: “Đạt” nếu lớn hơn, “Không đạt” nếu không.
Kết quả của bảng tính
Những sai lầm thường gặp khi sử dụng hàm Index Match trong Excel
Khi kết hợp hàm Index và hàm Match, bạn có thể gặp phải những lỗi như sau:
Lỗi #REF
Lỗi #REF thường xuất hiện khi phạm vi trả về của Index khác với phạm vi tra cứu của Match.
Nếu không tìm thấy dữ liệu đã cho, hàm Index và Match trả về #REF. Để khắc phục lỗi #REF, thay đổi phạm vi xét đến của hàm Index.
Lỗi #N/A
Lỗi #N/A xuất hiện khi không tìm thấy giá trị trong bảng dữ liệu, giống như lỗi thường gặp ở hàm Vlookup.
Tại sao nên ưu tiên hàm Index Match thay vì Vlookup và Hlookup?
Kết hợp hàm Index và hàm Match mang lại nhiều lợi ích hơn:
- Hàm Vlookup và Hlookup chỉ tra cứu dữ liệu theo một chiều, khó sử dụng cho dữ liệu 2 chiều.
- Sử dụng Vlookup và Hlookup dễ gây sai sót khi thêm/bớt cột trong bảng tính.
Điểm chốt
Hy vọng thông tin đã giúp bạn hiểu rõ hàm Index Match và cách kết hợp 2 hàm này. Nếu có thắc mắc hoặc muốn cập nhật thêm thông tin, ghé thăm Mytour ngay nhé.
