Hàm Index Match được sử dụng trong Excel rất nhiều và mang đến nhiều lợi ích thiết thực cho người dùng. Mặc dù là hai hàm khác nhau nhưng khi kết hợp với nhau lại tạo ra nhiều giá trị tìm kiếm hiệu quả nhất. Vậy đây là hàm tính gì? Sử dụng kết hợp ra sao? Để rõ chi tiết hơn bạn đọc hãy cùng với Sforum tìm hiểu xem ngay bài viết dưới đây nhé. Show
Hàm Index Match là gì?Hàm Index Match là 2 hàm riêng lẻ kết hợp với nhau để tạo ra hiệu quả trong việc dò tìm dữ liệu. Để hiểu đây là hàm gì bạn hãy phân tích từng loại nhé. Cụ thể: Hàm MatchHàm Match là một hàm tìm kiếm giá trị được xác định trước trong một phạm vi ô. Tiếp đến chúng sẽ trả về đúng vị trí tương đối của giá trị trong phạm vi đó. Công thức cú pháp hàm là: \=MATCH(Lookup_value,Lookup_array,[Match_type]). Trong đó:
Hàm IndexHàm Index là một dạng hàm trả về mảng. Chúng giúp lấy các giá trị tại một ô nào đó giao giữa cột và dòng. Công thức cú pháp hàm: \=INDEX(Array,Row_num,[Column_num]) Trong đó:
Cú pháp hàm INDEX dạng tham chiếu được tính như sau: \=INDEX(Reference,Row_num,[Column_num],[Area_num]) Trong đó:
Cách kết hợp hàm Index và Match trong ExcelĐể có thể sử dụng hàm Index Match trong Excel sẽ có VD minh họa để bạn dễ hiểu. Cụ thể như sau: 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: Bước 1: Trong bảng dữ liệu Excel bạn nhập công thức =INDEX(C3:C8,MATCH(C10,B3:B8,0)). Công thức này sẽ được nhập vào ô tham chiếu mà bạn muốn hiển thị kết quả. Nhập ô tham chiếu công thức =INDEX(C3:C8,MATCH(C10,B3:B8,0))Bước 2: Nhập công thức xong bạn nhấn phím Enter để chúng hiển thị kết quả tính. Như vậy là bạn đã sử dụng kết hợp 2 hàm đó để tìm kết quả dữ liệu rồi đó. Nhấn Enter để hiển thị kết quả cần tìm kiếmCá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 sẽ có ví dụ minh họa cụ thể như sau: Ví dụ: Bạn 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 bạn sẽ cần phải 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) thì 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 2Các kết hợp 2 hàm Index và hàm Match để dò tìm kết quả giá trị cho ô D4 như sau: Bước 1: Bạn sử dụng hàm Match để xác định vị trí của hàng tương ứng sản phẩm CDRom. Khoảng giá trị phạm vi B15:B18 của bảng 2, công thức: \=MATCH(B4,$B$15:$B$18,0). Kết quả “4” tương ứng với hàng số 4 trong bảng 2 xét đến. Nhập công thức tính hàm Match =MATCH(B4,$B$15:$B$18,0)Bước 2: Tiếp đến bạn sử dụng hàm Match để xác định vị trí cột tương ứng của hãng sản xuất Samsung. Phạm vi xét đến dữ liệu trong khoảng B15:E15. Công thức nhập: \=MATCH(C4,$B$15:$E$15,0). Kết quả cho giá trị “2” tương ứng với cột số 2 trong bảng 2. Nhập công thức tính hàm Match =MATCH(C4,$B$15:$E$15,0)Bước 3: Sau đó bạn sử dụng hàm Index và kết hợp với 2 hàm Match đã tính ở trên. Khi đó sẽ tìm được 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 matchKế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 bạn 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 đến. Copy công thức để hiển thị toàn bộ kết quả dữ liệuCách kết hợp hàm Index Match và VlookupĐể kết hợp hàm Index Match Vlookup thì sẽ có công thức tính riêng. Công thức như sau: \=VLOOKUP(INDEX(tham_chiếu;[hàng];[cột]);table_array;col_index_num;[range_lookup]) Trong đó:
Ví dụ minh họa: Có bảng dữ liệu dùng để xác định điểm vùng của từng học sinh dựa vào khu vực. Bảng dữ liệu ví dụ minh họaCông thức áp dụng: \=VLOOKUP(INDEX(A2:F9;1;6);$I$2:$J$9;2;0) Trong đó:
Hướng dẫn sử dụng hàm Index Match kết hợp IfĐể sử dụng kết hợp giữa hàm Index Match và hàm If sẽ có công thức tính như 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 đó:
Ví dụ minh họa: Bảng dữ liệu đưa ra xác định sản phẩm bánh quy đã bán đạt hay không đạt chỉ tiêu KPI. Để có thể đạt chỉ tiêu KPI số lượng bánh bán ra phải lớn hơn 10 sản phẩm. Bảng ví dụ minh họaCô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 sẽ trả về địa chỉ hàng của ô chứa giá trị bánh quy. Hàm Index sẽ trả về số lượng bán của bánh quy. Đem so sánh xem lớn hơn 10 sản phẩm hay không. Nếu đúng lớn hơn thì giá trị trả về “Đạt” còn sai thì giá trị trả về “không đạt”. Kết quả bảng tính nhận đượcMột số lỗi thường gặp khi sử dụng hàm Index Match trong ExcelKhi sử dụng kết hợp hàm Index và hàm Match trong Excel sẽ gặp một số lỗi cơ bản thường thấy như sau: Lỗi REFLỗi REF này là một trong những lỗi phổ biến thường gặp nhất khi sử dụng kết hợp 2 hàm tính Index và Match. Thông thường, lỗi sẽ xảy ra khi phạm vi trả về của hàm Index khác với phạm vi tra cứu của hàm Match.Đối với trường hợp không tìm thấy tiêu chí dữ liệu đã cho thì sử dụng hàm Index và Match sẽ sẽ về REF. Muốn khắc phục được lỗiREF này thì giải pháp tốt nhất là thay đổi phạm vi xét đến của hàm Index.Lỗi N/ALỗi N/A nãy xuất hiện khi không tìm thấy giá trị xét đến của bảng dữ liệu. Lỗi này cũng xảy ra tương tự giống như hàm Vlookup thường thấy.Vì sao nên dùng hàm Index Match thay Vlookup và Hlookup?Việc sử dụng kết hợp hàm Index và hàm Match thay thế cho hàm Vlookup, hàm Hlookup sẽ tạo ra nhiều hiệu quả. Sự kết hợp hai hàm này sẽ tạo ra nhiều lợi ích tốt hơn với lý do đơn giản sau:
Lời kếtHy vọng với toàn bộ thông tin tổng hợp trên đã phần nào giúp bạn đọc hiểu rõ hàm Index Match là gì rồi chứ. Bên cạnh đó còn biết được cách sử dụng kết hợp 2 hàm này đơn giản nhất. Nếu có gì thắc mắc với thông tin trên hoặc cập nhập thêm nhiều vấn đề khác bạn hãy đến với Sforum ngay nhé. |