Cách kết hợp hàm Match và Index , Vlookup, Hlookup trong Excel để tra cứu dữ liệu

Để tra cứu dữ liệu với nhiều điều kiện khác nhau, ta có thể:

• Sử dụng hàm MATCH kết hợp với hàm INDEX để thay thế cho việc sử dụng Hlookup và Vlookup.
• Hoặc kết hợp hàm Match với hàm VLOOKUP và HLOOKUP để tăng linh hoạt hơn khi tìm kiếm như tham chiếu tới nhiều kết quả trong cả cột và hàng.

Sau đây mình sẽ hướng dẫn cách để áp dụng Hàm Match kết hợp với các hàm trên:

1. Kết hợp hàm MATCH và INDEX

Sử dụng hàm MATCH kết hợp với hàm INDEX có nhiều ưu điểm hơn so với việc chỉ tra cứu dữ liệu bằng hàm HLOOKUP và VLOOKUP như:

• Có thể tra cứu ở bất kỳ cột nào, không cần cột đầu tiên.
• Có thể tra cứu cả hai chiều (hàng và cột).
• Ít lỗi hơn và không bị ảnh hưởng khi thêm hoặc xóa cột trong bảng.

1.1. Tổng quan về hàm MATCH và INDEX

Hàm INDEX

Hàm INDEX trả về giá trị của một ô hoặc một phạm vi dựa trên số thứ tự hàng và cột với cú pháp như sau:

=INDEX(array, row_num, [column_num])

Trong đó:

• array: Phạm vi chứa dữ liệu (ví dụ: A1:C10).
• row_num: Số thứ tự của hàng trong phạm vi.
• column_num: Số thứ tự của cột trong phạm vi (tùy chọn, nếu bỏ qua, trả về toàn bộ hàng).

Ví dụ:

Với dữ liệu bảng trên, công thức =INDEX(A2:C3, 2, 3) trả về 90 (hàng 2, cột 3 trong phạm vi A2:C3).

Hàm MATCH

Hàm MATCH tìm vị trí (số thứ tự) của một giá trị trong một phạm vi với cú pháp như sau:

=MATCH(lookup_value, lookup_array, [match_type])

Trong đó:

• lookup_value: Giá trị cần tìm.
• lookup_array: Phạm vi chứa các giá trị để tìm kiếm.
• match_type:
  • 0: Tìm chính xác (phổ biến nhất).
  • 1: Tìm giá trị nhỏ nhất gần bằng (yêu cầu sắp xếp tăng dần).
  • -1: Tìm giá trị lớn nhất gần bằng (yêu cầu sắp xếp giảm dần).

Ví dụ vẫn với bảng dữ liệu trên:

Với phạm vi A2:A3 là {An, Bình}, công thức =MATCH("Bình", A2:A3, 0) trả về 2.

Sau đây ta sẽ tìm hiểu từng trường hợp sử dụng kết hợp hàm MATCH và INDEX:

Trường hợp 1: Hàm MATCH và INDEX tra cứu dữ liệu dựa trên 1 điều kiện

Ứng dụng: Sử dụng để tìm giá trị (như điểm số) dựa trên 1 điều kiện duy nhất thay thế cho VLOOKUP.

Hàm chung: =INDEX(cột chứa giá trị cần lấy, MATCH(điều kiện, cột chứa tiêu chí, 0)

Ví dụ với bảng dữ liệu sau:

Ta cần tìm điểm số của Bình thì ta sẽ sử dụng công thức như sau:

=INDEX(C2:C4, MATCH("Bình", A2:A4, 0))

Trong đó:

• Cột chứa tiêu chí: A2:A4 (Tên)
• Cột chứa giá trị cần lấy: C2:C4 (Điểm)
• Hàm =MATCH(“Bình”, A2:A4, 0) để tìm vị trí của “Bình”

Kết quả của công thức trên như sau:

Nếu trong tình trạng kết quả trả về bị lỗi:

• #N/A: Nếu “Bình” không có trong cột A, ta cần kiểm tra chính tả hoặc thêm hàm IFERROR với công thức như sau =IFERROR(INDEX(C2:C4, MATCH("Bình", A2:A4, 0)), "Không tìm thấy") để khi không tìm thấy thì kết quả sẽ trả về là “Không tìm thấy” thay cho lỗi #N/A.
• #REF: chứng tỏ phạm vi chứa giá trị không đúng (ở trong ví dụ trên là C2:C4), ta cần kiểm tra và chọn đúng phạm vi giá trị.

Trường hợp 2: Sử dụng MATCH và INDEX để tra cứu hai chiều (theo hàng và cột)

Ứng dụng: Giúp tìm giá trị tại giao điểm của một hàng và một cột cụ thể (ví dụ: điểm của một học sinh trong một tháng).

Hàm chung: =INDEX(phạm vi dữ liệu chính, MATCH("điều kiện 1", cột tiêu chí (hàng), 0), MATCH("điều kiện 2", hàng tiêu chí (cột), 0))

Ví dụ với bảng dữ liệu sau:

Để tìm điểm của “Bình” trong “Tháng 2” ta sử dụng công thức sau:

=INDEX(B2:D4, MATCH("Bình", A2:A4, 0), MATCH("Tháng 2", B1:D1, 0))

Trong đó:

• Phạm vi dữ liệu chính: B2:D4
• Cột tiêu chí (hàng): A2:A4 (Tên)
• Hàng tiêu chí (cột): B1:D1 (Tháng)
• =MATCH("Bình", A2:A4, 0) để tìm vị trí hàng của “Bình”
• =MATCH("Tháng 2", B1:D1, 0) để tìm vị trí cột của “Tháng 2”

Kết quả khi áp dụng công thức trên như sau:

Nếu kết quả công thức trả về lỗi:

• #N/A: Kiểm tra chính tả của “Bình” hoặc “Tháng 2”, hoặc đảm bảo chúng tồn tại trong phạm vi tra cứu.
• #VALUE: Nếu phạm vi không đồng nhất (ví dụ: B2:D4 chứa giá trị không hợp lệ), kiểm tra dữ liệu.

Mẹo hay:

• Sử dụng tham chiếu ô (như E1 cho tên, F1 cho tháng) để tạo công thức động: =INDEX(B2:D4, MATCH(E1, A2:A4, 0), MATCH(F1, B1:D1, 0))
• Cố định phạm vi bằng $ để sao chép công thức mà không làm thay đổi phạm vi.

Biến thể từ công thức trên:

• Ta có thể sử dụng để tra cứu toàn bộ hàng: Nếu bỏ tham số cột trong INDEX (=INDEX(B2:D4, MATCH("Bình", A2:A4, 0), 0)), trả về {92, 87, 91}.

Trường hợp 3: Sử dụng MATCH và INDEX với nhiều điều kiện

Ứng dụng: Sử dụng tìm giá trị thỏa mãn nhiều điều kiện (từ 2 điều kiện trở lên, thường là 3 và 4 điều kiện).

Cách thức hiện:

• Cách 1: Tạo cột phụ để nối các điều kiện với nhau để đơn giản hóa về cách sử dụng hàm MATCH và INDEX cho 2 điều kiện.
• Cách 2: Sử dụng mảng để kết hợp các tiêu chí.

Dưới đây là bảng dữ liệu mẫu và mình sẽ giới thiệu cách để áp dụng hàm MATCH và INDEX cho nhiều điều kiện:

Cách 1: Sử dụng cột phụ

Ví dụ với yêu cầu muốn tìm điểm của “Bình” trong môn “Lý” ta sẽ tạo cột phụ (ví dụ cột E) để nối các tiêu chí với công thức như sau: =A2&B2

Sử dụng công thức MATCH để tìm vị trí của “BìnhLý” như sau: =MATCH("Bình"&"Lý", E2:E5, 0)

Kết hợp với hàm INDEX để ra công thức đầy đủ như sau:

=INDEX(D2:D5, MATCH("Bình"&"Lý", E2:E5, 0))

Với việc sử dụng công thức trên, kết quả sẽ trả về như sau:

Cách 2: Sử dụng mảng dữ liệu để kết hợp tiêu chí (chỉ áp dụng với Excel 365/2021 trở lên)

Ví dụ vẫn yêu cầu tìm điểm của “Bình” trong môn “Lý” ta sẽ sử dụng mảng để kết hợp tiêu chí với công thức đầy đủ như sau:

=INDEX(D2:D5, MATCH(1, (A2:A5="Bình")*(B2:B5="Lý"), 0))

Trong đó:

• (A2:A5="Bình") tạo mảng {FALSE, FALSE, TRUE, TRUE}
• (B2:B5="Lý") tạo mảng {FALSE, TRUE, FALSE, TRUE}
• (A2:A5="Bình")*(B2:B5="Lý") tạo mảng {0, 0, 0, 1}
• MATCH(1, ..., 0) tìm vị trí của 1 (kết quả: 4)
• INDEX(D2:D5, 4) trả về 92

kết quả của việc sử dụng hàm mảng tiêu chí như sau:

Nếu kết quả trả về lỗi:

• #N/A: Kiểm tra xem tiêu chí có tồn tại không. Sử dụng IFERROR để xử lý với công thức: =IFERROR(INDEX(D2:D5, MATCH(1, (A2:A5="Bình")*(B2:B5="Lý"), 0)), "Không tìm thấy")
• #VALUE: Đảm bảo các phạm vi có cùng kích thước.

Trong trường hợp muốn thêm điều kiện thứ 3 (ví dụ như năm) thì ta sẽ sử dụng mảng điều kiện như sau: (A2:A5="Bình")*(B2:B5="Lý")*(C2:C5=2023)

Trường hợp 4: Sử dụng MATCH và INDEX để tra cứu giá trị gần đúng

Ứng dụng: Tìm giá trị dựa trên một tiêu chí gần đúng (ví dụ: mức lương dựa trên số năm kinh nghiệm)

Ví dụ với bảng dữ liệu về mức lương theo năm như sau:

Với bảng trên, nếu ta muốn tìm mức lương của người có 4 năm kinh nghiệm thì ta sẽ sử dụng công thức như sau:

=INDEX(B2:B5, MATCH(4, A2:A5, 1))

Trong đó:

• Viết công thức MATCH với match_type = 1: =MATCH(4, A2:A5, 1) kết quả sẽ trả về 2 vì 4 lớn hơn 3 nhưng nhỏ hơn 5.

Lưu ý rằng: ta phải đảm bảo Cột A với dữ liệu đối chiếu được sắp sếp theo thứ tự tăng dần.

 

2. Cách sử dụng hàm MATCH với VLOOKUP và HLOOKUP

2.1. Cú pháp hàm và cách dùng

Cú pháp của hàm Match: MATCH(lookup_value;lookup_array;[match_type])

Trong đó:

• Lookup_value: là giá trị cần tìm. Có thể là dạng số, chữ hoặc ô tham chiếu
• Lookup_array: là dãy các ô cần tìm kiếm
• Match_type: xác định loại tìm kiếm. Tham số không bắt buộc. Có thể nhận một trong các giá trị sau: 1, 0, -1 Nếu bạn không điền giá trị, Excel sẽ mặc định kiểu khớp =1.
  • 1 hoặc không ghi gì: tìm giá trị lớn nhất trong dãy tìm kiếm sao cho giá trị đó nhỏ hơn hoặc bằng giá trị tìm kiếm. Loại này yêu cầu sắp xếp dãy tìm kiếm theo thứ tự tăng dần, từ nhỏ nhất đến lớn nhất hoặc từ A đến Z
  • 0: tìm giá trị đầu tiên trong dãy bằng đúng giá trị tìm kiếm. Không yêu cầu sắp xếp dãy tìm kiếm
  • -1: tìm giá trị nhỏ nhất trong dãy lớn hơn hoặc bằng giá trị tìm kiếm. Dãy tìm kiếm nên được sắp xếp theo thứ tự giảm dần, từ to nhất đến nhỏ nhất hoặc từ Z đến A.’

Lưu ý: 

• Hàm MATCH chỉ trả lại vị trí tương đối của giá trị cần tìm kiếm trong một dãy, không phải giá trị của chính nó
  Hàm Match không phân biệt chữ in hoa và chữ thường.
• Nếu chuỗi tìm kiếm chứa một vài giá trị tìm kiếm, vị trí của giá trị đầu tiên sẽ được trả về
  Nếu không tìm thấy giá trị phù hợp, kết quả sẽ trả về giá trị lỗi #N/A.
• Nếu bạn dùng match_type =1 , Mảng tìm kiếm phải được sắp xếp theo thứ tự tăng dần.
• Nếu bạn dùng match_type = -1 , Mảng tìm kiếm phải được sắp xếp theo thứ tự giảm dần.
• Nếu lookup_value là dạng chữ thì phải cho vào dấu ngoặc kép “”.
• Nếu match_type = 0 và lookup_value là chuỗi văn bản, thì bạn có thể dùng ký tự dấu hỏi (?) để đại diện cho bất kì kí tự đơn nào và dấu sao (*) phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực, hãy gõ một dấu ngã (~) trước ký tự đó.
• Nếu trong mảng tìm kiếm lookup_array có nhiều hơn một giá trị của lookup_value, thì kết quả sẽ trả về kết quả nhỏ nhất.

Ví dụ: Tên hàng ở cột B và Doanh thu các năm ở các cột C, D, E. Để tìm ra vị trí của loại hàng (ví dụ Bia lon), sử dụng công thức sau: =MATCH(B11;B2:B7;0)

Công thức MATCH cho chúng ta biết rằng Bia long đứng ở vị trí thứ 3 trong dãy giá trị tìm kiếm.

2.2. Hàm Match kết hợp với hàm Vlookup

Hàm MATCH được dùng để xác định vị trí tương đối của giá trị tìm kiếm, do đó hoàn toàn phù hợp đối với col_index_num của hàm VLOOKUP. Do đó thay vì chỉ rõ cột trả lại như một số không thay đổi, bạn sử dụng hàm MATCH để biết vị trí hiện tại của cột đó.

Ví dụ ta cần tìm Doanh thu 2018 của Bia lon. Thông thường ta sẽ có công thức sau:

=VLOOKUP(A11;A1:D7;3;FALSE)

Tham số col_index_num được đặt là 3 vì Doanh thu 2018 mà chúng ta muốn kéo nằm ở cột thứ 3 trong bảng.

Nhưng nếu bạn chèn thêm cột hoặc xóa bớt cột trong bảng đi. Thì kết quả sẽ không còn đúng nữa. Do cột Doanh thu 2018 không còn là cột thứ 3 của bảng.

Để giải quyết điều này, ta chỉ cần thêm hàm Match kết hợp vào hàm Vlookup

=MATCH(B10;A1:D1;0)

Trong đó:

• B10: là tên chính xác của cột cần tìm kiếm
• A1:D1: là dãy tìm kiếm chứa bảng

Sau đó gộp hàm Match vào tham số col_index_num của hàm Vlookup như sau:

=VLOOKUP(A11;A1:D7;MATCH(B10;A1:D1;0);FALSE)

Lúc này hàm của chúng ta có thể tự điều chỉnh tham chiếu dù có chèn thêm hay xóa bớt bao nhiêu cột đi nữa.

4.3. Hàm Match kết hợp với hàm Hlookup

Tương tự như hàm Vlookup, ta cũng có thể dùng hàm Match để kết hợp với hàm HLOOKUP. Ta sử dụng hàm Match để lấy vị trí tương đối của cột cần trả lại, và cung cấp số của cột đó cho tham số row_index_number của hàm Hlookup.

Công thức hàm Hlookup trong trường hợp này sẽ như sau:

=HLOOKUP(B10;A1:D7;MATCH(A11;A1:A7;0);FALSE)

Trên đây là cách để sử dụng hàm MATCH kết hợp với hàm INDEX, HLOOKUP và VLOOKUP để tra cứu dữ liệu viều các điều kiện khác nhau. Chúc các bạn áp dụng thành công!