Hướng dẫn cách dùng hàm MATCH trong Excel - Công thức và ví dụ cụ thể

Trong bài học Excel ngày hôm nay chúng ta sẽ cùng Gitiho khám phá hàm MATCH trong Excel - một hàm thuộc nhóm tìm kiếm và trả về dữ liệu, cùng với những ứng dụng tuyệt vời khi kết hợp hàm này với các hàm khác của Excel nhé.

Hàm MATCH là gì?

Hàm MATCH là hàm đối chiếu giá trị trong Excel có chức năng tìm kiếm một giá trị được xác định trong một phạm vi trang tính và trả về vị trí tương đối của giá trị đó ngay cả với các bảng, các mảng số liệu phức tạp. 

Cú pháp hàm MATCH trong Excel là gì?

Hàm MATCH có cú pháp như sau:

=MATCH(lookup_value,lookup_array,[match_type])

Trong đó:

• lookup_value: Là giá trị cần tìm kiếm trong mảng. 
• lookup_array: Là mảng, hay phạm vi tìm kiếm giá trị lookup_value.
• match_type: Là kiểu tìm kiếm. Bạn có thể chọn 1 trong 3 kiểu tìm kiếm dưới đây, hoặc bỏ qua tham số này.
  • = 1 (kiểu mặc định): tìm kiếm giá trị lớn nhất trong mảng lookup_array đáp ứng điều kiện nhỏ hơn hoặc bằng giá trị lookup_value. (phải sắp xếp dữ liệu theo thứ tự tăng dần A-Z, FALSE và TRUE)
  • = 0: tìm kiếm giá trị đầu tiên xuất hiện trong mảng lookup_array đáp ứng điều kiện bằng giá trị lookup_value. 
  • = -1: tìm kiếm giá trị nhỏ nhất trong mảng lookup_array đáp ứng điều kiện lớn hơn hoặc bằng giá trị lookup_value. (phải sắp xếp dữ liệu theo thứ tự giảm dần, Z-A, TRUE và FALSE)

Xem thêm: Hướng dẫn cách dùng hàm Match tìm kiếm nhiều điều kiện

Ví dụ cơ bản về cách dùng hàm MATCH

Giả sử chúng ta có một bảng điểm tổng kết môn được sắp xếp theo thứ tự giảm dần như sau:

Với bảng dữ liệu trên, nhiệm vụ của chúng ta là xác định vị trí của một học sinh bất kỳ trong bảng. Hãy lấy ví dụ học sinh tên Nhung. Làm thế nào để biết được điểm của học sinh Nhung đang xếp vị trí thứ mấy trong bảng điểm tổng hợp?

Để tìm vị trí xếp hạng của học sinh Nhung, chúng ta sẽ cần đến cách sử dụng hàm MATCH cho ô E3 như sau:

=MATCH(E2,A2:A11,0)

Trong đó: 

• E2: Giá trị tham chiếu đến ô chứa tên học sinh cần tìm.
• A2:A11: Phạm vi tìm kiếm.
• 0: Tìm kiếm giá trị chính xác tuyệt đối.

Kết quả cho thấy học sinh Nhung xếp vị trí thứ 5 trong cột Học sinh.

Một số lưu ý với hàm MATCH trong Excel

Khi sử dụng hàm MATCH trong Excel, các bạn hãy lưu ý một số đặc điểm sau đây của hàm:

• Hàm MATCH trả về vị trí tương đối chứa giá trị cần tìm, chứ không trả về chính giá trị đó.
• Hàm MATCH không phân biệt chữ hoa và chữ thường.
• Hàm MATCH có thể tìm kiếm các giá trị văn bản chứa giá trị đặc biệt.

Ký tự hoa thị "*" xác định look_value là một chuỗi ký tự, 

Ký tự hỏi chấm "?" xác định lookup_value là ký tự đơn. Bạn sẽ cần sử dụng ký tự dấu ngã "~" trước ký tự "?".

• Nếu trong phạm vi chứa nhiều hơn một giá trị khớp với, hàm MATCH trong Excel luôn trả về giá trị đầu tiên.
• Nếu trong phạm vi không chứa giá trị, hàm tìm kiếm trong Excel trả về lỗi #N/A.

Xem thêm: CÁCH ỨNG DỤNG HÀM INDEX VÀ MATCH NÂNG CAO (PHẦN 1)

Cách tối ưu hóa công thức hàm MATCH

Hàm MATCH với các ký tự đại diện

MATCH Exccel có khả năng hiểu các ký tự đại diện như dấu hoa thị "*" thay cho một chuỗi ký tự, dấu hỏi chấm "?" thay cho một ký tự đơn. Phương pháp này đặc biệt hữu ích trong các trường hợp tìm kiếm mà bạn chỉ nhớ được một phần trong chuỗi văn bản cần tìm.

Lưu ý: Khi sử dụng ký tự đặc biệt trong công thức hàm MATCH, bắt buộc phải nhập match_type là 0.

Ví dụ:

Giả sử bạn cần tìm kiếm vị trí của một học sinh dựa trên bảng điểm nhưng lại không thể nhớ chính xác tên học sinh này. Thay vì đó, bạn chỉ nhớ tên học sinh bắt đầu bằng các ký tự "Nh". Để giải quyết trường hợp này, bạn sẽ phải dùng hàm MATCH với công thức như sau:

=MATCH("Nh*",A2:A11,0)

Nếu bạn đã nhập giá trị "Nh*" vào ô E2 (như hình), bạn không cần nhập lại giá trị này vào công thức hàm MATCH trong Excel nữa. Thay vào đó, công thức của bạn sẽ là:

=MATCH(E2,A2:A11,0) 

Bên cạnh đó, bạn cũng có thể nhập giá trị "Nh" vào ô E2 và sử dụng ký tự "&" trong công thức hàm MATCH để kết hợp hai điều kiện cho tham số lookup_value:

=MATCH(E2&"*",A2:A11,0)

Lúc này, hàm MATCH trả về kết quả vị trí số 5 tương ứng với tên học sinh Nhung. 

Trong một trường hợp khác, bạn quên mất một ký tự nhất định trong giá trị cần tìm. Hãy học cách sử dụng hàm MATCH với dấu hỏi chấm "?" để đại diện cho ký tự bạn đã quên. Giả sử chúng ta có một công thức như sau:

=MATCH("Nh?ng",A2:A11,0)

Nếu bạn đã nhập vào ô E2 giá trị "Nh?ng", bạn hoàn toàn có thể tham chiếu tới ô đó trong tham số lookup_value:

=MATCH(E2,A2:11,0)

Có thể nói, dù bạn không nhớ chính xác giá trị cần tìm, hàm MATCH trong Excel vẫn có khả năng tham chiếu bảng dữ liệu và trả về vị trí tương đối của giá trị khớp với tham số lookup_value.

Hàm MATCH phân biệt chữ hoa và chữ thường

Hàm MATCH không có chức năng phân biệt chữ hoa và chữ thường. Vậy chúng ta sẽ cần kết hợp thêm hàm EXACT nếu yêu cầu phân biệt 2 kiểu chữ. Công thức tổng quát của chúng ta như sau:

=MATCH(TRUE,EXACT(lookup_array,lookup_value),0)

Trong đó:

• Hàm EXACT so sánh giá trị tìm kiếm với mọi giá trị trong phạm vi. Nếu tìm được giá trị chính xác hoàn toàn (phân biệt chữ hoa và chữ thường), hàm EXACT trả về giá trị TRUE. Ngược lại, giá trị nhận được là FALSE.
• Hàm MATCH sẽ tiến hành so sánh giá trị tìm kiếm TRUE với kết quả của hàm EXACT, sau đó trả về vị trí tương đối của giá trị trùng khớp đầu tiên.

Lưu ý: Công thức hàm MATCH trên là công thức mảng, do đó, chúng ta cần nhấn tổ hợp Ctrl+Shift+Enter để công thức chạy. Nếu không, Excel sẽ trả về lỗi #N/A.

Ví dụ:

Giả sử chúng ta có bảng tổng hợp số liệu kinh doanh dựa vào mã hàng như trong hình.

Để giải yêu cầu này, chúng ta sẽ tạo công thức kết hợp MATCH và EXACT như sau:

=MATCH(TRUE,EXACT(A2:A10,E2),0)

Kết quả khi kết hợp hàm MATCH và hàm EXACT là sản phẩm có mã B-201 đang đứng ở vị trí thứ 6 trong bảng dữ liệu tổng hợp. 

Xem thêm: 3 cách chuyển chữ thường thành chữ hoa trong Excel và ngược lại

Các phép kết hợp hàm MATCH với các hàm tìm kiếm trong Excel

Bạn có thắc mắc về khác biệt của hàm MATCH so với các hàm tìm kiếm khác trong Excel? Công dụng khác nhau của các hàm là gì? Khi nào nên sử dụng? Tìm hiểu ngay!

Hàm MATCH kết hợp hàm VLOOKUP

Hàm VLOOKUP tuy là một hàm tham chiếu phổ biến trong Excel nhưng còn rất nhiều điểm hạn chế, chẳng hạn như hàm sẽ lỗi khi chúng ta đã thêm hoặc bớt một số cột trong phạm vi tìm kiếm.

Để khắc phục, hàm MATCH chắc chắn sẽ là một trợ thủ hoàn hảo bởi khả năng xác định vị trí tương đối của giá trị tìm kiếm. Và khi hàm MATCH kết hợp VLOOKUP sẽ giải quyết rắc rối thay đổi cấu trúc các cột trên trang tính.

Ví dụ:

Giả sử ta có 2 đầu điểm Toán và Văn. Giá trị cần tìm là điểm Văn của em học sinh tên Nhung. Đầu tiên, chúng ta sẽ sử dụng công thức hàm VLOOKUP thông thường:

=VLOOKUP(F2,A2:C11,3,FALSE)

Nhưng nếu chúng ta xóa cột điểm Toán khỏi bảng, bạn sẽ thấy lỗi #REF! hiện lên tại ô F2. 

Lỗi này bắt nguồn từ việc công thức VLOOKUP trên lấy dữ liệu ở cột thứ 3 trong bảng, tuy nhiên, bảng điểm lúc này chỉ còn lại 2 cột do đã xóa đi 1 cột trước đó.

Để xử lý vấn đề này, hãy áp dụng công thức hàm MATCH kết hợp VLOOKUP như sau:

=VLOOKUP(E2,A1:C11,MATCH(D3,A1:C1,0),FALSE)

Trong đó: 

=MATCH(D3,A1:C1,0) dùng để tìm vị trí mà giá trị trong ô D3 (điểm Văn) khớp chính xác với một trong các ô trong phạm vi A1 đến C1. Cho dù bạn thêm bớt cột, hàm vẫn trả về giá trị mà bạn cần tìm.

Kết quả của công thức hàm MATCH kết hợp VLOOKUP như hình:

Hàm MATCH kết hợp hàm HLOOKUP

Hàm HLOOKUP tìm kiếm ngang có một nhược điểm là sẽ ngừng hoạt động sau khi người dùng thêm hoặc bớt các dòng trong trang tính Excel. Và chúng ta lại cần đến hàm MATCH để kết hợp với HLOOKUP.

Hàm MATCH sẽ tự động xác định vị trí hàng chứa thông tin cần tham chiếu, thay vì nhập vị trí hàng cố định tại tham số row_index_col. Do đó, chúng ta sẽ tránh được lỗi #REF! xảy ra khi thay đổi cấu trúc các hàng Excel.

Ví dụ tìm điểm Văn của học sinh tên Nhung:

Chúng ta có công thức hàm HLOOKUP cơ bản như sau:

=HLOOKUP(B5, B1:K3,3,FALSE)

Tương tự VLOOKUP, khi xóa đi dòng Điểm Toán, chúng ta lại nhận được lỗi #REF!

Khi bạn gặp phải lỗi này, thì hãy nhờ đến hàm MATCH như sau:

=HLOOKUP(B5,B1:K3,MATCH(A6,A1:A3,0),FALSE)

Trong đó: MATCH(A6,A1:A3,0) tìm kiếm giá trị của ô A6 (Văn) khớp chính xác trong phạm vi ô A1: A3.

Hàm kết hợp HLOOKUP và MATCH vẫn trả về kết quả đúng khi chúng ta xóa đi hàng điểm Toán:

Hàm MATCH kết hợp hàm INDEX

 Hàm INDEX trả về giá trị tại giao điểm một hàng và một cột với cú pháp như sau:

=INDEX (array, row_num, [column_num])

Hãy cùng tìm hiểu phép kết hợp hàm MATCH và hàm INDEX qua từng trường hợp cụ thể nhé.

Kết hợp MATCH INDEX để tìm kiếm từ phải sang trái

Hàm LOOKUP (VLOOKUP, HLOOKUP) không thể tìm kiếm dữ liệu nếu giá trị cần tìm được đặt trong cột bên trái dải ô đã xác định. Sử dụng hàm INDEX MATCH sẽ giúp bạn khắc phục nhược điểm này:

Giả sử chúng ta có một bảng thông tin như sau:

Yêu cầu đề bài là xác định chủ nhân của một Email cụ thể. Vì vị trí cột Họ và tên nằm bên trái cột Email khiến hàm LOOKUP cũ không thể hoạt động. 

Thay vào đó, hàm MATCH và hàm INDEX có thể làm được. Hãy điền vào ô F3:

=INDEX(A1:A6,MATCH(F2,C1:C6,0))

Trong đó:

• Hàm MATCH tại tham số row_num có nhiệm vụ tìm kiếm vị trí tương đối của giá trị trong bảng khớp hoàn toàn với tên Email cần tìm tại ô F2.
• Hàm INDEX tìm kiếm giá trị nằm trong phạm vi cột Họ và tên (cột A), tại vị trí tương ứng với kết quả hàm MATCH trong Excel.

Xem thêm: Giới thiệu các tính năng hữu dụng của hàm INDEX trong Excel

Tìm kiếm sau khi thêm hoặc xóa cột/hàng

Bên cạnh phương pháp sử dụng hàm MATCH kết hợp VLOOKUP và HLOOKUP, chúng mình sẽ đề xuất một giải pháp toàn diện hơn: hàm INDEX MATCH. 

Ví dụ: Tìm Điểm Văn của bạn học sinh có tên Nhung

Công thức hàm INDEX và MATCH như sau:

=INDEX(C2:C11,MATCH(F2,A2:A11,0))

Kết quả như sau:

Ngay khi chúng ta thêm một cột điểm Anh vào bảng, công thức hàm INDEX MATCH sẽ tự động được cập nhật mà không hề phải sửa đổi thủ công. 

Tìm kiếm theo nhiều điều kiện

Phép kết hợp hàm MATCH và hàm INDEX để tìm kiếm theo nhiều điều kiện sẽ phức tạp hơn một chút.

Ví dụ:

Chúng ta cần trích xuất đơn giá dựa theo 2 điều kiện: Mã hàng và mã công ty. 

• Bước 1: Tạo công thức hàm MATCH xác định vị trí Mã công ty trong bảng.

=MATCH(H3,A3:A6,0)

• Bước 2: Tạo công thức hàm MATCH trong Excel xác định vị trí Mã hàng trong bảng.

=MATCH(H2,A2:E2,0)

• Bước 3: Sau khi đã tạo 2 công thức hàm MATCH lần lượt cho các tham số row_num và col_num, chúng ta sẽ hoàn thiện công thức hàm INDEX trả về giá trị cần tìm như sau:

=INDEX(B3:E6,MATCH(H3,A3:A6,0),MATCH(H2,B2:E2,0))

Hàm INDEX sẽ phụ trách phần việc cuối cùng là tìm kiếm và trả về kết quả nằm tại giao điểm của hàng và cột dựa trên kết quả các công thức hàm MATCH tại tham số tương ứng.

Tìm kiếm kết hợp tính toán

Hàm INDEX MATCH có khả năng kết hợp các hàm tính toán MIN, MAX và AVERAGE vào công thức hàm tìm kiếm trong Excel.

Ví dụ: Giả sử chúng ta có bảng điểm tổng kết của một lớp học và cần tìm các học sinh sở hữu số điểm cao nhấp, thấp nhất, đồng thời học sinh có số điểm gần với trung bình cả lớp nhất.

Bước 1: Để xác định học sinh có điểm tổng kết cao nhất trong bảng, chúng ta sử dụng hàm INDEX và MATCH với công thức như sau:

=INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0))

Trong đó:

• Hàm MATCH trong Excel đặt tại tham số row_num của hàm INDEX có nhiệm vụ xác định vị trí tương đối của giá trị lớn nhất trong phạm vi B2:B11.
• Hàm INDEX sau đó tham chiếu đến cột Học sinh (A2:A11) để tìm giá trị tương ứng với kết quả hàm MATCH.

Bước 2: Tương tự, học sinh có số điểm tổng kết thấp nhất trong bảng sẽ được tìm thấy bằng công thức dưới đây:

=INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0))

Bước 3: Để tìm học sinh có số điểm gần với trung bình lớp nhất, chúng ta cần sắp xếp dữ liệu trong bảng về thứ tự điểm tăng dần, sau đó áp dụng công thức hàm MATCH kết hợp INDEX:

=INDEX(A2:A11,MATCH(AVERAGE(B2:B11),B2:B11,1))

Chúng ta sử dụng match_type = 1 đối với công thức này để tìm kiếm giá trị tương đối. Đây là lý do chúng ta phải sắp xếp dữ liệu trong bảng để đảm bảo kết quả hàm INDEX MATCH trả về chính xác 100%.

Có thể nói phép kết hợp hàm INDEX và MATCH giúp xử lý dữ liệu phức tạp nhanh chóng, hiệu quả hơn so với LOOKUP. Đặc biệt, khi dữ liệu quá lớn, vượt quá giới hạn 255 ký tự của VLOOKUP và HLOOKUP,  thì INDEX MATCH là giải pháp tốt hơn để thay thế.

Xem thêm: Hướng dẫn cách kết hợp hàm INDEX và MATCH thay thế hàm Vlookup trong Excel

Hàm MATCH kết hợp hàm ISNA

Hàm MATCH khi kết hợp với ISNA đóng vai trò tìm sự khớp và khác biệt dữ liệu cột trong bảng.

Còn hàm ISNA dùng để kiểm tra lỗi #N/A trong giá trị với công thức hàm như sau:

=ISNA(value)

Trong đó: value là giá trị cần kiểm tra, có thể là văn bản, số, ngày tháng, hoặc tham chiếu ô.

Để đánh dấu các giá trị tại cột 2 không tồn tại trong cột 1, hãy sử dụng cú pháp hàm logic được tạo bởi hàm MATCH và hàm ISNA như sau:

=IF(ISNA(MATCH(giá trị đầu tiên trong cột 1, cột 2, 0)), “Không có trong cột 1”, “”)

Giả sử chúng ta có 2 cột mã các khóa học Tuyệt đỉnh Excel của Gitiho như trong hình dưới đây:

Điền công thức vào các ô tương ứng của cột D (từ D2 đến D6):

=IF(ISNA(MATCH(B2,A:A,0)),"Không có trong cột 1","")

Trong đó:

MATCH(B2, A:A, 0):  tìm kiếm giá trị trong ô B2 trong cột A. Tham số 0 cho biết tìm kiếm chính xác.

ISNA(...): kiểm tra xem kết quả của hàm MATCH có là lỗi #N/A (không tìm thấy) hay không. Nếu tìm thấy lỗi, hàm ISNA sẽ trả về TRUE, ngược lại sẽ trả về FALSE.

IF(ISNA(...), "Không có trong cột 1", ""): Hàm IF kiểm tra kết quả từ hàm ISNA. Nếu kết quả là TRUE, thì hàm IF sẽ trả về chuỗi "Không có trong cột 1". Ngược lại, nếu là FALSE thì hàm IF sẽ trả về một chuỗi trống.

Chúng ta nhận được kết quả: Giá trị "EXG05" và giá trị "EXG06" chỉ xuất hiện trong cột 2 mà không hề tồn tại trong cột 1.

Ví dụ khác: yêu cầu phân biệt sự khác nhau chính xác, bao gồm cả chữ hoa và chữ thường. 

Hãy áp dụng thêm hàm EXACT vào công thức hàm MATCH để có thể phân biệt chữ hoa và chữ thường như sau:

=IF(ISNA(MATCH(TRUE,EXACT(A:A,B2),0)),"Không có trong cột 1", "")

Lưu ý: Đây là công thức mảng, do đó, hãy ghi nhớ rằng bạn cần nhấn tổ hợp phím Ctrl+Shift+Enter để Excel chạy hàm và trả về kết quả đúng.

Kết quả sau khi áp dụng hàm như hình bên dưới:

Như vậy, các giá trị duy nhất tại cột 2 lần lượt là "EXG01", "exg01" và "ExG02". 

Ngoài ra, chúng ta còn có một phương pháp khác với hàm ROWS và COLUMNS. Bạn còn có thể dễ dàng phát hiện sự chênh lệch giữa số lượng ký tự giữa giá trị trong các cột. Hãy tìm hiểu cách thực hiện trong bài viết dưới đây nhé!

Xem thêm: Hướng dẫn giải bài tập Excel với hàm INDEX và hàm MATCH chi tiết

Tổng kết

Trong bài viết ngày hôm nay, chúng ta đã tìm hiểu về hàm MATCH trong Excel và các ứng dụng vô cùng lợi hại của hàm MATCH với vai trò là hàm lấy giá trị của ô trong Excel.