Cách kết hợp hàm INDEX và MATCH trong Excel nhiều điều kiện

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é.

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 MATCH

Hà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 đó:

• Lookup_value: Là khoảng giá trị tìm kiếm trong mảng Lookup_array. Giá trị tìm kiếm đó có thể là số, văn bản, giá trị logic hay 1 tham chiếu ô đến một số nào đó. Đó cũng có thể là tham chiếu đến văn bản hay giá trị logic của dữ liệu.
• Lookup_array: Là mảng hay một phạm vị ô được tìm kiếm trên điều kiện.
• MATCH_type: Là kiểu tìm kiếm đang xét.

Hàm INDEX

Hà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 đó:

• Array: Là vùng ô hoặc một hàng số mảng nào đó bắt buộc đang tìm kiếm.
• Row_num: Là chọn hàng trong mảng tìm kiếm từ đó trả về một giá trị.
• Column_num: Là chọn cột trong mảng tìm kiếm từ đó trả về một giá trị.

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 đó:

• Reference: Là vùng tham chiếu bắt buộc được xét đến.
• Row_num: Là chỉ số hàng tìm kiếm từ đó trả về một tham chiếu.
• Column_num: Là chỉ số cột tìm kiếm từ đó trả về một tham chiếu.
• Area_num: Là số của vùng ô dữ liệu sẽ trả về giá trị trong Reference. Nếu Area_num được bỏ qua thì hàm INDEX sẽ dùng vùng 1.

Khi nào nên kết hợp hàm INDEX và MATCH trong Excel?

Kết hợp hàm INDEX và MATCH trong Excel là lựa chọn tối ưu khi bạn cần truy xuất dữ liệu chính xác từ các bảng có cấu trúc phức tạp. Sự kết hợp này đặc biệt hữu ích cho việc tìm kiếm theo tiêu chí đa chiều mà không giới hạn bởi vị trí cố định của cột hay hàng.

Nó vượt trội hơn hàm VLOOKUP hay HLOOKUP vì cho phép tìm kiếm linh hoạt theo cả chiều ngang và dọc, tự động điều chỉnh theo sự thay đổi của dữ liệu. Điều này không chỉ tăng cường tính linh hoạt và hiệu quả trong việc xử lý dữ liệu mà còn nâng cao độ chính xác của thông tin trích xuất, làm cho công thức của bạn trở nên mạnh mẽ hơn trong việc đối phó với các yêu cầu dữ liệu phức tạp.

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ả.

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 đó.

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 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).

Cá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.

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.

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))

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 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.

Cá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 đó:

• INDEX(tham_chiếu; [hàng]; [cột]): Là giá trị dùng để dò tìm điều kiện.
• Table_array: Là bảng chứa giá trị muốn dò tìm dữ liệu.
• Col_INDEX_num: Là vị trí của cột chứa giá trị mà bạn muốn dò tìm.
• [range_lookup]: Là phạm vi mà dữ liệu đang tìm kiếm. Nếu có giá trị 0 là dò tìm tương đối hoặc 1 là dò tìm tuyệt đối.

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.

Công thức áp dụng:

=VLOOKUP(INDEX(A2:F9;1;6);$I$2:$J$9;2;0)

Trong đó:

• INDEX(A2:F9;1;6): Nằm trong khu vực dải ô A2:F9 lấy ra giá trị ở hàng 1 cột 6.
• $I$2:$J$9: Dải ô trải dài từ I2:J9 có chứa giá trị dò tìm.
• 2: Là vị trí cột điểm vùng.
• 0: Là kiểu dò tìm tương đối.

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 đó:

• Mảng_dữ_liệu: Là khu vực vùng ô hoặc 1 hàng số mảng nào đó bắt buộc.
• Khóa_tìm_kiếm: Là giá trị mà bạn muốn tìm kiếm dữ liệu.
• Dải_ô: Là 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. Dải ô này chỉ có thể có duy nhất là 1 hàng hoặc 1 cột nào đó.
• Loại_tìm_kiếm: Là phương thức tìm kiếm với 3 dạng chính. Bao gồm: 0 kết quả khớp chính xác, 1 là mặc định giá trị. Dạng thứ ba -1 là dải ô được sắp xếp theo thứ tự giảm dần trả về giá trị nhỏ nhất lớn hơn hoặc bằng khóa tìm kiếm.
• Số_cột: Giá trị trả về số cột của dữ liệu lấy ra.
• Giá_trị_nếu_đúng: Ở đây sẽ trả về giá trị nếu hàm INDEX + điều_kiện đúng.
• Giá_trị_nếu_sai: Ở đây sẽ trả về giá trị nếu hàm INDEX + điều_kiện sai.

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.

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 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”.

Một số lỗi thường gặp khi sử dụng hàm INDEX MATCH trong Excel

Khi 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 #REF

Lỗ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ỗi #REF 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/A

Lỗ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:

• Hàm VLOOKUP, hàm HLOOKUP thưởng chúng chỉ có thể ra cứu dữ liệu theo một chiều. Đối với những dữ liệu cần tra cứu 2 chiều rất khó để sử dụng.
• Sử dụng hàm VLOOKUP, hàm HLOOKUP sẽ dẫn đến sai sót nếu như bạn thêm hoặc bớt đi cột ở trong bảng tính.

Lời kết

Hy 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é.

• Xem thêm:Thủ thuật laptop.

[Product_Listing categoryid='878' propertyid=' customlink='https://cellphones.com.vn/laptop.html' title='Danh sách Laptop đang được quan tâm nhiều tại CellphoneS']