Bài viết dưới đây, chúng ta sẽ cùng nhau tìm hiểu về cách sử dụng hàm MATCH trong Excel, cùng đón đọc nhé!
Hàm Match, cú pháp và cách sử dụng
Hàm MATCH trong Excel có công dụng tìm kiếm một giá trị cụ thể trong một dãy những ô, và đưa ra vị trí tương đối của giá trị đó.
Cú pháp như sau:
- MATCH (lookup_value, lookup_array, [match_type])
- Lookup_value(bắt buộc): là giá trị mà bạn muốn tìm. Có thể đó là là số, chữ, giá trị logic hoặc là ô tham chiếu
- Lookup_array (bắt buộc): là dãy những ô cần tìm kiếm
Match_type (tuỳ chọn): xác định loại tìm kiếm, một trong những giá trị sau: 1, 0, -1. Khi bạn đặt match_type bằng 0, giá trị trả về sẽ là chính xác, trong khi đó hai loại còn lại cho giá trị xấp xỉ.
- 1 hoặc là không ghi gì (mặc định): tìm giá trị lớn nhất tại dãy tìm kiếm với điều kiện giá trị đó nhỏ hơn hoặc là bằng giá trị tìm kiếm. Loại này yêu cầu phải sắp xếp dãy tìm kiếm theo thứ tự tăng dần, từ bé nhất đến lớn nhất hoặc là 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 là bằng giá trị tìm kiếm. Dãy tìm kiếm cũng nên được sắp xếp theo thứ tự giảm dần, từ to nhất đến nhỏ nhất hoặc là từ Z đến A.
Để bạn có thể hiểu hơn hàm MATCH excel, hãy tạo một công thức đơn giản dựa trên dữ liệu sau đây: Tên học sinh ở cột A và điểm ở cột B, thực hiện sắp xếp theo thứ tự giảm dần. Để có thể tìm ra vị trí một học sinh cụ thể (ví dụ Hoàng), hãy dùng công thức sau:
=MATCH (“Hoàng”, A2:A8, 0)
Ở ảnh mình họa trên, tên học sinh đã đđược nhập vào dãy ngẫu nhiên, vì thế bạn đặt match_type bằng 0 bởi vì loại này không yêu cầu phải sắp xếp giá trị trong dãy tìm kiếm. Công thức MATCH cho bạn 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
Bạn có thể thấy rằng việc dùng hàm MATCH Excel không hề khó. Tuy vậy, có những điều sau bạn nên chú ý:
- Hàm MATCH Excel chỉ có thể trả lại vị trí tương đối của giá trị cần tìm tại một dãy, không phải giá trị của chính nó
- Hàm MATCH không thể phân biệt giữa chữ in hoa và chữ thường khi tiến hành xử lý dữ liệu dạng chữ
- Nếu chuỗi tìm kiếm có chứa một vài giá trị tìm kiếm thì vị trí của giá trị đầu tiên sẽ được trả về
- Nếu như giá trị tìm kiếm không được tìm thấy ở trong chuỗi tìm kiếm, thì 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 với các hàm Excel khác, hàm MATCH excel cũng hiểu những kí tự đại diện sau:
- Dấu hỏi (?): là thay thế cho một ký tự
- Dấu hoa thị (*): là thay thế cho một chuỗi các kí tự
Lưu ý: Ký tự đại diện chỉ được dùng trong công thức hàm MATCH excel với match_type là 0.
Công thức Match có kí tự đại diện được dùng ở trường hợp bạn muốn tìm vị trí của một vài kí tự hoặc là một phần của chuỗi văn bản. Để có thể minh hoạ rõ hơn,bạn hãy xem ví dụ sau
Ví dụ như bạn có một danh sách các người bán trong khu vực cùng doanh số bán hàng của họ trong tháng vừa rồi. Bạn đabg muốn tìm vị trí tương đối của một người bán xác định nào đó trong danh sách nhưng lại không thể nhớ chính xác tên anh ta, nhưng bạn lại nhớ được một vài chữ cái trong tên của anh ấy.
Tên của anh ta nằm trong dãy A2:A11, và bạn đang tiến hành tìm kiếm tên bắt đầu với từ “Ho”, công thức như sau:
=MATCH (“Ho*”, A2:A11, 0)
Để cho công thức được linh hoạt hơn, bạn có thể thực hiện đánh giá trị tìm kiếm vào một vài ô (như ô E1 trong ví dụ này), và tiến hành ghép ô đó với kí tự đại diện như sau
=MATCH (E1& “*”, A2:A11, 0)
Như ảnh minh họa bên dưới, công thức trả lại giá trị, nó là vị trí của “Hoàng”
Để có thay thế một kí tự trong giá trị tìm kiếm, bạn hãy dùng kí tự đại diện “?” như sau
=MATCH (“m?nh”, A2:A11, 0)
Công thức trên được hợp với tên Minh và chạy lại vị trí tương đối của công thức là 5
Hàm Match phân biệt kiểu chữ
Như đã có nhắc ngay đầu, hàm MATCH trong Excel sẽ không phân biệt được chữ in hoa và chữ thường.Vì thế, để có thể tạo công thức Match phân biệt được hai loại kí tự này, bạn nên dùng hàm MATCH kết hợp cùng với hàm EXACT so sánh những ô 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 sẽ là:
=MATCH (TRUE, EXACT (dãy tìm kiếm, giá trị tìm kiếm), 0)
Công thức nãy chạy được khi
- Hàm EXACT thực hiện 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 như ô được so sánh bằng chính xác, hàm sẽ trả giá trị TRUE, ngược lại là FALSE
- Sau đó, hàm MATCH Excel sẽ 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ằng hàm EXACT, và trả lại vị trí của giá trị trùng đầu tiên.
Bạn cũng nên nhớ rằng đây là công thức chuỗi nên bạn cần phải thực hiện ấn tổ hợp phím Ctrl + Shift + Enter
Ví dụ 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 lúc này sẽ như sau
=MATCH (TRUE, EXACT (A2:A9, E1), 0)
Ảnh chụp dưới đây minh hoạ cho công thức Match giúp phân biệt dạng chữ trong Excel
So sánh hai cột tìm sự giống và khác nhau bằng hàm Isna/Match
Cú pháp thực hiện 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 cứ giá trị nào của cột 2 mà không có trong cột 1, thì công thức trả lại là “Không có trong cột 1”. Một số giải thích cho công thức này như sau:
- Hàm Match sẽ tiến hành tìm từng giá trị ở cột 1 trong cột 2, nếu như giá trị được tìm thấy, thì hàm Match sẽ trả về vị trí tương đối của giá trị này, còn không thì lỗi #N/A sẽ được trả về.
- Hàm ISNA thực hiện kiểm tra xem kết quả trả về từ hàm Match có phải là #N/A hay không. Nếu như hàm ISNA trả về giá trị đúng, tức là giá trị không được tìm thấy, thì hàm trả về là TRUE, và ngược lại sẽ là FALSE. Ở ví dụ này, TRUE có nghĩa là một giá trị ở cột 1 không được tìm thấy trong cột 2 do đó lỗi #N/A sẽ được trả về bằng hàm MATCH
- Việc nhìn thấy TRUE cho những giá trị không xuất hiện ở cột 1 có thể sẽ gây nhầm lẫn cho người dùng. Bạn cũng có thể tận dụng hàm IF để thực hiện hiển thị “Không có trong cột 1” hoặc là bất cứ chữ nào bạn muốn
Ví dụ như 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 sẽ ở dạng sau:
=IF (ISNA (MATCH (B2, A:A, 0)), “Không có trong cột 1”, “”)
Hàm MATCH sẽ không phân biệt được dạng chữ. Vì vậy, để có thể phân biệt dạng chữ, bạn hãy thực hiện gắn thêm hàm EXACT vào chuỗi tìm kiếm lookup_array, và sau đó nhấn tổ hợp Ctrl + Shift + Enter
=IF (ISNA (MATCH (TRUE, EXACT (A:A, B2), 0)), “Không có trong cột 1”, “”)
Ảnh chụp dưới đây thể hiện cả hai công thức:
Xem thêm: Cách viết số mũ bằng hàm POWER trong Excel
Hàm VlookUp và Match
Trong bài viết này, mặc định là bạn đã có kiếm kiến thức cơ bản về cách sử dụng hàm VLOOKUP trong Excel.
Một trong các khuyết điểm lớn nhất của hàm VLOOKUP đó chính là nó dừng làm việc ngay khi chèn hoặc là xoá một cột trong bảng tìm kiếm. Đó chính là vì VLOOKUP kéo một giá trị trùng dựa vào số của cột mà bạn xác định, Excel cũng sẽ không thể thực hiện điều chỉnh số khi một hoặc nhiều cột mới được thêm vào hoặc là xoá bớt đi khỏi bảng.
Hàm MATCH được sử dụng để xác định vị trí tương đối của giá trị tìm kiếm, vì thế hoàn toàn phù hợp với col_index_num của hàm VLOOKUP. Hay cách khác, thay cho việc chỉ rõ cột trả lại như một số không thay đổi, bạn có thể dùng hàm MATCH để biết được vị trí hiện tại của cột đó.
Để bạn hiểu dễ dàng hơn, hãy cùng xem lại ví dụ về điểm của học sinh. Tuy nhiên lần này bạn sẽ gọi điểm của học sinh mà không phải là vị trí tương đối như lần trước.
Ví dụ như giá trị tìm kiếm ở ô F1, dãy bảng là A1:C2, công thức sẽ như sau:
=VLOOKUP (F1, A1:C8, 3, FALSE)
Tham số thứ 3 (col_index_num) được đặt là 3 bởi vì Điểm toán mà bạn muốn kéo nằm ở cột thứ 3 trong bảng. Trong hình bên dưới, hàm Vlookup làm việc bình thường
cho đến khi bạn thực hiện chèn thêm hoặc là xoá bớt cột
Vậy tại sao lỗi #REF lại có thể xảy ra? Đó chính là vì tham số col_index_num khi được đặt bởi 3 đã thông báo cho Excel để có thể lấy một giá trị từ cột thứ 3, trong khi hiện tại thì lại chỉ có 2 cột trong bảng.
Để có thể giải quyết vấn đề này, bạn hãy phát triển hàm Vlookup bằng việc thêm hàm Match:
MATCH (E2, A1:C1, 0)
Trong đó:
- E2 là giá trị tìm kiếm, hay là tên chính xác của cột trả lại, ví dụ như cột mà bạn muốn lấy giá trị, ở đây sẽ là cột “Điểm Toán”
- A1:C1 là dãy tìm kiếm có chứa bảng
Sau đó tiến hành gộp hàm Match vào tham số col_index_num của hàm Vlookup sau đây:
=VLOOKUP (F1, A:C8, MATCH (E2, A1:C1, O), FALSE)
Và chắc chắn là hàm sẽ làm việc tốt dù cho bạn có thêm hay là xoá bao nhiêu cột
Trong hình minh họa ở trên, chúng ta đã xoá toàn bộ những ô tham chiếu để hàm hoạt động tốt kể cả khi người dùng chuyển đến vị trí khác trong worksheet. Và bạn có thể thấy rằng công thức hoạt động được sau khi xoá một cột và Excel còn có thể tự điều chỉnh tham chiếu ở trường hợp này.
Hàm HlookUp và Match
Tương tự như hàm Vlookup, bạn cũng có thể dùng hàm Match để phát triển công thức HLOOKUP. Nguyên tắc chung cũng sẽ giống như hàm Vlookup: bạn dùng hàm Match để thực hiện lấy vị trí tương đối của cột cần trả lại, rồi sau đó cung cấp số của cột đó cho tham số row_index_number của hàm Hlookup
Ví dụ giá trị tìm kiếm tại ô B5, bảng B1:h3, tên của hàng trả lại (cũng chính là giá trị tìm kiếm của hàm MATCH) ở ô A6 và tiêu đề hàng là A1:A3, lúc này công thức hoàn chỉnh như sau:
=HLOOKUP (B5, B1:H3, MATCH (A6, A1:A3, 0), FALSE)
Vậy là sự kết hợp giữa hàm Hlookup/Vlookup cùng hàm Match đã giúp phát triển hàm Hlookup và Vlookup. Tuy vậy, hàm MATCH không thể loại bỏ hết toàn bộ các khuyết điểm. Để có thể giải quyết vấn đề này, bạn nên tìm hiểu cách sử dụng hàm INDEX MATCH, được đánh giá là 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. Đừng quên tìm hiểu thêm nhiều kiến thức tin học bổ ích khác từ chúng tôi nhé!
Xem thêm: Cách dùng hàm tính nhân trong Excel
