Để thực hiện tra cứu phức tạp với nhiều tiêu chí, ta không thể sử dụng hàm VLOOKUP. Khi đó, chúng ta phải sử dụng hàm INDEX kết hợp với hàm MATCH để dò tìm nhiều điều kiện nâng cao. Sau đây, Unica sẽ Hướng dẫn cách kết hợp hàm index và match trong excel như thế nào. Cùng tìm hiểu ngay.
Hàm INDEX và hàm MATCH
Ở phần này, chúng tôi sẽ giới thiệu về hàm INDEX và hàm MATCH trong excel.
Công thức hàm INDEX
Hàm INDEX trong excel có chức năng trả về giá trị của một ô hoặc một phạm vi ô trong một bảng tính, dựa trên chỉ số hàng và cột được chỉ định. Công thức hàm INDEX có hai dạng:
- INDEX (array, row_num, [column_num]): Trả về giá trị của một ô trong một mảng hai chiều, dựa trên chỉ số hàng và cột.
- INDEX (reference, row_num, [column_num], [area_num]): Trả về giá trị của một ô trong một hoặc nhiều phạm vi tham chiếu, dựa trên chỉ số hàng, cột và khu vực.
Trong đó:
-
array: Là mảng hai chiều chứa các giá trị cần tìm.
-
reference: Là một hoặc nhiều phạm vi tham chiếu chứa các giá trị cần tìm.
-
row_num: Là chỉ số hàng của ô cần tìm trong mảng hoặc phạm vi tham chiếu.
-
column_num: Là chỉ số cột của ô cần tìm trong mảng hoặc phạm vi tham chiếu. Nếu bỏ qua, hàm sẽ trả về giá trị của cột đầu tiên.
-
area_num: Là chỉ số khu vực của phạm vi tham chiếu cần tìm. Nếu bỏ qua, hàm sẽ trả về giá trị của khu vực đầu tiên.
Hàm INDEX trong excel có chức năng trả về giá trị của một ô
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
Công thức hàm MATCH
Hàm MATCH trong excel có chức năng trả về vị trí của một giá trị trong một mảng một chiều hoặc trong một phạm vi ô. Công thức hàm MATCH có dạng:
MATCH (lookup_value, lookup_array, [match_type])
Trong đó:
-
lookup_value: Là giá trị cần tìm kiếm trong mảng hoặc phạm vi ô.
-
lookup_array: Là mảng một chiều hoặc phạm vi ô chứa các giá trị cần so sánh với lookup_value.
-
match_type: Là kiểu khớp giá trị, có ba giá trị có thể là:
- 1: Trả về vị trí của giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value. Mảng hoặc phạm vi ô phải được sắp xếp theo thứ tự tăng dần.
- 0: Trả về vị trí của giá trị bằng lookup_value. Mảng hoặc phạm vi ô không cần được sắp xếp theo thứ tự nào.
- -1: Trả về vị trí của giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value. Mảng hoặc phạm vi ô phải được sắp xếp theo thứ tự giảm dần.
Hàm MATCH trong excel có chức năng trả về vị trí của một giá trị trong một mảng một chiều hoặc trong một phạm vi ô
Vì sao nên dùng hàm Index Match thay Vlookup và Hlookup?
Hàm Index Match là một thuật toán kết hợp hai hàm INDEX và MATCH để tìm kiếm và lấy dữ liệu từ một bảng tính theo điều kiện cho trước. Hàm Index Match có nhiều ưu điểm so với hàm Vlookup và Hlookup như:
-
Không phụ thuộc vào vị trí của cột hoặc hàng chứa giá trị cần tìm. Hàm Hlookup và Vlookup chỉ có thể tìm kiếm giá trị trong cột đầu tiên hoặc hàng đầu tiên của mảng hoặc phạm vi ô, trong khi hàm Index Match có thể tìm kiếm giá trị ở bất kỳ vị trí nào.
-
Không bị ảnh hưởng bởi việc thêm hoặc xóa cột hoặc hàng trong bảng tính. Hàm Vlookup và Hlookup cần phải chỉ định số thứ tự của cột hoặc hàng chứa giá trị cần lấy, nên nếu có sự thay đổi về số lượng cột hoặc hàng, công thức sẽ bị sai. Hàm Index Match không cần phải chỉ định số thứ tự của cột hoặc hàng, mà chỉ cần dùng hàm MATCH để tìm ra vị trí của chúng.
-
Có thể tìm kiếm giá trị theo nhiều điều kiện. Hàm Vlookup và Hlookup chỉ có thể tìm kiếm giá trị theo một điều kiện duy nhất, là giá trị của cột hoặc hàng chứa giá trị cần tìm. Hàm Index Match có thể kết hợp với các hàm khác như AND, OR, IF để tìm kiếm giá trị theo nhiều điều kiện khác nhau.
Lý do nên dùng hàm INDEX và MATCH
Ví dụ hàm INDEX kết hợp hàm MATCH
Ở phần này, chúng tôi sẽ giới thiệu tới bạn 4 ví dụ về hàm index và hàm match trong Excel để bạn hiểu hơn về sự kết hợp của hai hàm này.
Cách kết hợp hàm index và match trong excel
Để kết hợp hàm index và match trong excel, ta có thể sử dụng công thức sau:
-
INDEX (array, MATCH (lookup_value, lookup_array, match_type), [column_num]): Trả về giá trị của một ô trong một mảng hai chiều, dựa trên giá trị của một ô khác trong mảng đó và chỉ số cột được chỉ định.
-
INDEX (reference, MATCH (lookup_value, lookup_array, match_type), [column_num], [area_num]): Trả về giá trị của một ô trong một hoặc nhiều phạm vi tham chiếu, dựa trên giá trị của một ô khác trong phạm vi đó và chỉ số cột và khu vực được chỉ định.
Ví dụ: Cho bảng dữ liệu 1:
Cho bảng dữ liệu
Yêu cầu: Tim điểm của Bình bằng cách dùng hàm index và match.
Cách kết hợp hàm index và match trong excel:
Bước 1: Nhập công thức sau vào ô B9:
=INDEX(D3:D6;MATCH(B8;A3:A6;0)).
Nhập công thức sau vào ô B9
Trong đó:
-
D3:D6: là mảng hai chiều chứa các giá trị cần tìm.
-
B8: là giá trị cần tìm kiếm trong mảng.
-
A3:A6: là mảng một chiều chứa các giá trị cần so sánh với B8.
-
0: là kiểu khớp giá trị chính xác.
Bước 2: Nhấn Enter sẽ thu được kết quả là 7 điểm.
Kết quả sau khi nhập công thức
Cách kết hợp hàm index và match trong excel với nhiều điều kiện
Bài tập hàm index và match: Cho hai bảng dữ liệu như sau:
Cho hai bảng dữ liệu
Yêu cầu: Dùng hàm index và match để tìm đơn giá cho từng sản phẩm ở bảng 1.
Cách sử dụng hàm index match nhiều điều kiện:
Bước 1: Ở ô D4, bạn nhập công thức kết hợp của hai hàm index và match như sau:
=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 vào ô D4
Bước 2: Nhấn enter, 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 3: Copy công thức ở ô D4 xuống phía dưới sẽ thu được bảng kết quả như sau:
Copy công thức ở ô D4 xuống phía dưới
Cách kết hợp hàm Index Match và Vlookup
Hàm Index Match và Vlookup có thể kết hợp với nhau để tìm kiếm và lấy dữ liệu từ nhiều bảng tính khác nhau, dựa trên một giá trị chung.
Ví dụ: Cho bảng ví dụ 3 và 4:
Cho bảng ví dụ 3 và 4
Yêu cầu: Từ bảng ví dụ 4 hãy dùng Hàm Index Match và Vlookup để tìm ra mức trợ cấp của từng người ở bảng số 3.
Cách thực hiện:
Bước 1: Ở ô D18, bạn nhập công thức sau:
=VLOOKUP(INDEX(A19:C23;1;3);$F$19:$G$22;2;0).
Nhập công thức vào D18
Trong đó:
- INDEX(A19:C23;1;3): Nằm trong khu vực dải ô A19:C23 lấy ra giá trị ở hàng 1 cột 3.
- $F$19:$G$22: Dải ô trải dài từ F19:G22 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.
Bước 2: Nhấn enter sẽ thu được kết quả là 170000 VNĐ.
Kết quả là 170000 VNĐ
Bước 3: Kéo thả chuột xuống phía dưới sẽ thu được bảng kết quả là:
Kéo thả chuột xuống phía dưới
Hướng dẫn sử dụng hàm Index Match kết hợp If
Hàm Index Match kết hợp If có thể giúp ta tìm kiếm và lấy dữ liệu từ một bảng tính theo một điều kiện nào đó.
Ví dụ: Cho bảng dữ liệu 5:
Cho bảng dữ liệu
Yêu cầu: Dùng hàm if kết hợp với Index Match để xác định lượng cam bán ra có đạt yêu cầu hay không. Lưu ý, lượng trái cây bán ra cần lớn hơn 8kg mới được coi là đạt yêu cầu.
Cách thực hiện:
Bước 1: Đặt con trở chuột vào ô B38, nhập công thức sau:
=IF(INDEX(A32:D35;MATCH("Cam";A32:A35;3);3)>8;"Đạt";"Không đạt").
Nhập công thức vào ô B37
Bước 2: Nhấn Enter sẽ thu được kết quả là Đạt, tức là lượng cam bán ra đã nhiều hơn 8kg.
Kết quả là Đạt, tức là lượng cam bán ra đã nhiều hơn 8kg
Một số lưu ý khi thực hiện cách kết hợp hàm index và match trong excel
Khi sử dụng hàm Index Match, ta cần lưu ý một số điểm sau:
-
Hàm Index Match có thể tìm kiếm giá trị theo chiều ngang hoặc chiều dọc, tùy thuộc vào cách chọn mảng hoặc phạm vi ô. Nếu chọn một hàng hoặc một cột, hàm sẽ tìm kiếm theo chiều ngang hoặc chiều dọc tương ứng. Nếu chọn một mảng hai chiều, hàm sẽ tìm kiếm theo cả hai chiều.
-
Hàm Index Match có thể tìm kiếm giá trị trong bất kỳ vị trí nào của mảng hoặc phạm vi ô, không nhất thiết phải là cột đầu tiên hoặc hàng đầu tiên. Tuy nhiên, bạn cần chắc chắn rằng giá trị cần tìm kiếm là duy nhất trong mảng hoặc phạm vi ô, nếu không hàm sẽ trả về vị trí của giá trị đầu tiên được tìm thấy.
-
Hàm Index Match có thể tìm kiếm giá trị theo nhiều điều kiện khác nhau, bằng cách kết hợp với các hàm khác như AND, OR, IF. Tuy nhiên, bạn cần chú ý đến thứ tự ưu tiên của các toán tử và hàm khi viết công thức, để đảm bảo kết quả chính xác.
Một số lưu ý khi sử dụng hàm Index và Match
Một số lỗi thường gặp khi dùng hàm Index Match
Khi sử dụng hàm Index Match, ta có thể gặp phải một số lỗi sau:
Lỗi #n/a trong hàm index
Lỗi #NA xuất hiện khi hàm không tìm thấy giá trị cần tìm kiếm trong mảng hoặc phạm vi ô. Để khắc phục lỗi này, ta cần kiểm tra lại xem giá trị cần tìm kiếm có tồn tại trong mảng hoặc phạm vi ô hay không và xem có bị sai chính tả hay không. Ngoài ra, ta cũng cần xem lại kiểu khớp giá trị có phù hợp hay không, ví dụ nếu dùng kiểu khớp 1 hoặc -1, ta cần sắp xếp mảng hoặc phạm vi ô theo thứ tự tăng dần hoặc giảm dần.
Lỗi #NA xuất hiện khi hàm không tìm thấy giá trị cần tìm kiếm trong mảng hoặc phạm vi ô
Lỗi #VALUE
Lỗi #VALUE xuất hiện khi hàm có tham số không hợp lệ. Để khắc phục lỗi này, ta cần kiểm tra lại xem các tham số của hàm có đúng kiểu dữ liệu hay không, ví dụ nếu dùng số thực thay vì số nguyên hoặc dùng văn bản thay vì số. Ngoài ra, ta cũng cần xem lại xem các mảng hoặc phạm vi ô có cùng kích thước hay không, ví dụ nếu dùng một hàng và một cột có số lượng ô khác nhau.
Tổng kết
Hàm Index Match là một kỹ thuật hữu ích để tìm kiếm và lấy dữ liệu từ một bảng tính theo điều kiện cho trước. Hàm Index Match có nhiều ưu điểm so với hàm Vlookup và Hlookup, như không phụ thuộc vào vị trí của cột hoặc hàng, không bị ảnh hưởng bởi việc thêm hoặc xóa cột hoặc hàng, và có thể tìm kiếm giá trị theo nhiều điều kiện. Tuy nhiên, khi sử dụng hàm Index Match, bạn cần lưu ý một số điểm như cách chọn mảng hoặc phạm vi ô, cách sắp xếp mảng hoặc phạm vi ô, cách viết công thức, và cách khắc phục các lỗi thường gặp. Hy vọng bài viết này đã giúp bạn hiểu rõ hơn về cách kết hợp hàm index và match trong excel để dò nhiều điều kiện.
