Hàm Match và ứng dụng hay của hàm Match trong Excel 2007, 2010, 2013, 2016

Trong bài hướng dẫn này, Học Excel Online sẽ giải thích cách sử dụng hàm MATCH cùng các ví dụ của hàm. Đồng thời, bạn sẽ học được cách phát triển hàm lookup của riêng mình bằng cách tạo hàm dựa trên VLOOKUP và MATCH

Hàm Match trong Excel, cú pháp và cách sử dụng.

Hàm MATCH trong Excel giúp tìm kiếm một giá trị cụ thể trong một dãy các ô, và đưa ra vị trí tương đối của giá trị đó.

Cú pháp của hàm MATCH như sau:

• MATCH (lookup_value, lookup_array, [match_type])
• Lookup_value(bắt buộc): là giá trị bạn muốn tìm. Có thể là số, chữ, giá trị logic hoặc ô tham chiếu
• Lookup_array (bắt buộc): là dãy các ô cần tìm kiếm

Match_type (tuỳ chọn): xác định loại tìm kiếm. Có thể nhận một trong các giá trị sau: 1, 0, -1. Khi đặt match_type bằng 0, giá trị trả về là chính xác, trong khi hai loại còn lại cho giá trị xấp xỉ.

• 1 hoặc không ghi gì (mặc định): 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.

Để hiểu hơn hàm MATCH, các bạn có thể tạo một công thức đơn giản dựa trên dữ liệu sau: Tên học sinh ở cột A và điểm ở cột B, sắp xếp theo thứ tự giảm dần. Để tìm ra vị trí một học sinh cụ thể (ví dụ Hoàng), sử dụng công thức sau:

=MATCH (“Hoàng”, A2:A8, 0)

Trong bức ảnh trên, tên học sinh được nhập vào dãy một cách ngẫu nhiên, do đó chúng ta đặt match_type bằng 0 vì loại này không yêu cầu sắp xếp giá trị trong dãy tìm kiếm. Công thức MATCH cho chúng ta biết rằng Hoàng đứng ở vị trí thứ 7 trong dãy giá trị tìm kiếm.  

4 điền bạn cần biết về hàm Match

Như bạn đã biết, sử dụng hàm MATCH trong Excel không hề khó. Tuy nhiên, bạn nên chú ý những điều sau đây  

1. 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ó
2. Hàm MATCH không phân biệt giữa chữ in hoa và không in hoa khi xử lý dữ liệu dạng chữ
3. 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ề
4. Nếu giá trị tìm kiếm không được tìm thấy trong chuỗi tìm kiếm, lỗi #N/A sẽ được trả về

Ứng dụng của hàm Match trong Excel 

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

Giống như các hàm Excel khác, hàm MATCH hiểu những kí tự đại diện sau:

• Dấu hỏi (?): thay thế cho một ký tự
• Dấu hoa thị (*): thay thế cho một chuỗi các kí tự

Lưu  ý: Ký tự đại diện chỉ được sử dụng tỏng công thức hàm MATCH với match_type là 0.

Công thức Match với kí tự đại diện được dùng trong trường hợp bạn muốn tìm vị trí của một vài kí tự hoặc một phần của chuỗi văn bản. Để minh hoạ rõ hơn, chúng ta hãy xem ví dụ sau

Giả sử bạn có một danh sách những người bán trong khu vực và doanh số bán hàng của họ trong tháng vừa rồi. Bạn muốn tìm vị trí tương đối của một người bán nhất định trong danh sách nhưng không thể nhớ chính xác tên anh ta, nhưng bạn nhớ được một vài chữ cái trong tên của anh ấy.

Tên của người bán nằm trong dãy A2:A11, và bạn đang tìm kiếm tên bắt đầu với từ “Ho”, công thức sẽ như sau:

=MATCH (“Ho*”, A2:A11, 0)

Để công thức linh hoạt hơn, bạn có thể đánh giá trị tìm kiếm vào một vài ô (như ô E1 trong ví dụ này), và ghép ô đó với kí tự đại diện như sau

=MATCH (E1& “*”, A2:A11, 0)

Như hình chụp bên dưới, công thức trả lại giá trị , là vị trí của “Hoàng”

Để thay thế một kí tự trong giá trị tìm kiếm, sử dụng kí tự đại diện “?” như sau

=MATCH (“m?nh”, A2:A11, 0)

Công thức trên hợp với tên Minh và chạy lại vị trí tương đối của nó là 5

Hàm Match phân biệt kiểu chữ:

Như đã đề cập lúc đầu, hàm MATCH trong Excel không phân biệt được chữ in hoa và thường. Để tạo công thức Match phân biệt được hai loại kí tự này, các bạn nên sử dụng hàm MATCH kết hợp với hàm EXACT so sánh các ô chính xác, bao gồm dạng kí tự.

Công thức phân biệt dạng chữ để trùng với dữ liệu trong Excel là

=MATCH (TRUE, EXACT (dãy tìm kiếm, giá trị tìm kiếm), 0)

Công thức chạy được khi

• Hàm EXACT so sánh giá trị tìm kiếm với mỗi yếu tố của dãy tìm kiếm. Nếu ô được so sánh bằng chính xác, hàm trả giá trị TRUE, ngược lại là FALSE
• Sau đó, hàm MATCH so sánh TRUE (giá trị tìm kiếm của nó) với giá trị trong dãy được trả lại bởi hàm EXACT, và trả lại vị trí của giá trị trùng đầu tiên.

Các bạn nên nhớ đây là công thức chuỗi nên các bạn cần phải ấn tổ hợp phím Ctrl + Shift + Enter

Giả sử giá trị tìm kiếm của bạn ở ô E1 và chuỗi tìm kiếm là A2:A9, công thức như sau

=MATCH (TRUE, EXACT (A2:A9, E1), 0)

Hình chụp dưới đây minh hoạ cho công thức Match phân biệt dạng chữ trong Excel

Hướng dẫn học Excel 2016

So sánh hai cột tìm sự giống và khác nhau bằng hàm Isna/Match.

Cú pháp 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”, “”)

Đối với bất kì giá trị nào của cột 2 mà không có trong cột 1, công thức trả lại là “Không có trong cột 1”. Giải thích công thức này như sau:

• Hàm Match sẽ tìm từng giá trị ở cột 1 trong cột 2, nếu giá trị này được tìm thấy, hàm Match sẽ trả về vị trí tương đối của giá trị đó, nếu không tìm thấy, lỗi #N/A sẽ được trả về.
• Hàm ISNA kiểm tra xem kết quả trả về từ hàm Match có phải là #N/A hay không. Nếu hàm ISNA trả về giá trị đúng, nghĩa là giá trị không được tìm thấy, hàm trả về là TRUE, ngược lại là FALSE. Trong ví dụ này, TRUE nghĩa là một giá trị ở cột 1 không tìm thấy trong cột 2 do đó lỗi #N/A được trả về bởi hàm MATCH
• Việc nhìn thấy TRUE cho các giá trị không xuất hiện ở cột 1 có thể gây nhầm lẫn cho người sử dụng, bạm có thể tận dụng hàm IF để hiển thị “Không có trong cột 1” hoặc bất cứ chữ nào bạn muốn

Giả sử bạn đang so sánh giá trị ở cột B với các giá trị trong cột A, công thức có dạng sau:

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

Hàm MATCH trong Excel không phân biệt được dạng chữ. Do vậy, để phân biệt dạng chữ, ta gắn thêm hàm EXACT vào chuỗi tìm kiếm lookup_array, và nhớ ấn tổ hợp Ctrl + Shift + Enter

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

Hình chụp dưới đây cho thấy cả hai công thức

Xem thêm: hàm Excel cơ bản

Hàm VlookUp và Match

Ở bài viết này mặc định bạn đã có kiếm kiến thức cơ bản về hàm VLOOKUP trong Excel.

Một trong những khuyết điểm lớn nhất của hàm VLOOKUP  là nó dừng làm việc sau khi chèn hoặc xoá một cột trong bảng tìm kiếm. Đó là vì VLOOKUP kéo một giá trị trùng dựa trên số của cột mà bạn xác định, Excel sẽ không thể điều chỉnh số khi một hay nhiều cột mới được thêm vào hoặc xoá bớt đi khỏi bảng.

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. Nói cách khác, 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 đó.

Để hiểu dễ dàng hơn, chúng ta hãy cùng xem lại ví dụ về điểm của học sinh nhưng lần này chúng ta sẽ gọi điểm của học sinh mà không phải vị trí tương đối như lần trước.

Giả sử giá trị tìm kiếm ở ô F1, dãy bảng là A1:C2, công thức như sau:

=VLOOKUP (F1, A1:C8, 3, FALSE)

Tham số thứ 3 (col_index_num) được đặt là 3 vì Điểm toán mà chúng ta muốn kéo nằm ở cột thứ 3 trong bảng. Trong hình dưới đây, hàm Vlookup làm việc bình thường

cho đến khi bạn chèn thêm hoặc xoá bớt cột

Vậy tại sao lỗi #REF lại xảy ra? Đó là vì tham số col_index_num khi được đặt bằng 3 đã thông báo cho Excel để lấy một giá trị từ cột thứ 3, trong khi hiện tại lại chỉ có 2 cột trong bảng.

Để giải quyết vấn đề này, bạn có thể phát triển hàm Vlookup bằng cách thêm hàm Match:

MATCH (E2, A1:C1, 0)

Với:

• E2 là giá trị tìm kiếm, là tên chính xác của cột trả lại, ví dụ cột mà bạn muốn lấy giá trị, ở đây là cột “Điểm Toán”
• A1:C1 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 (F1, A:C8, MATCH (E2, A1:C1, O), FALSE)

Và chắc chắn rằng hàm sẽ làm việc tốt dù bạn có thêm hay xoá bao nhiêu cột

Trong hình chụp trên, Học Excel Online đã xoá tất cả các ô tham chiếu để hàm hoạt động tốt kể cả khi người sử dụng chuyển tới vị trí khác trong worksheet. Như bạn có thể thấy, công thức hoạt động được sau khi xoá một cột và Excel thậm chí còn có thể tự điều chỉnh tham chiếu trong trường hợp này.

Hướng dẫn học Excel 2010

Hàm HlookUp và Match

Tương tự như Vlookup, bạn cũng có thể sử dụng hàm Match để phát triển công thức HLOOKUP của mình. Nguyên tắc chung cũng giống như Vlookup: bạn 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

Giả sử giá trị tìm kiếm ở ô B5, bảng B1:h3, tên của hàng trả lại (cũng là giá trị tìm kiếm của hàm MATCH) ở ô A6 và tiêu đề hàng là A1:A3, công thức hoàn chỉnh như sau:

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

Như vậy, sự kết hợp giữa Hlookup/Vlookup với Match đã giúp phát triển hàm Hlookup và Vlookup. Tuy nhiên, hàm MATCH không thể loại bỏ hết tất cả các khuyết điểm. Để giải quyết vấn đề này, bạn nên thử sử dụng hàm INDEX MATCH, một công cụ thực sự mạnh và linh hoạt giúp tìm kiếm trong Excel và vượt trội hơn Vlookup và Hlookup trên nhiều phương diện.

Như vậy qua bài viết này, bạn đã biết làm thế nào để sử dụng hàm Match trong Excel. Hi vọng những ví dụ mà Học Excel Online cung cấp trong bài viết này sẽ giúp ích trong công việc của các bạn.

Xem thêm: Học Excel tại nhà với các chuyên gia