Microsoft Excel

Microsoft Excel (Bài 82): Hàm MATCH trong Excel

Posted on by HKT

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

KIỂM TRA XEM GIÁ TRỊ CÓ TỒN TẠI TRONG DỮ LIỆU GỐC HAY KHÔNG

Một trong những cách khác để tận dụng điểm mạnh của hàm Match đó là việc kiểm tra xem liệu có giá trị nào đó tồn tại hay không. Để làm được điều này bạn cần phải sử dụng điều kiện đối chiếu giá trị chính xác tuyệt đối trong phần match_type của hàm.

Trong ví dụ dưới đây, ta có 5 giá trị tham chiếu cần kiểm tra xem có thuộc tập hợp dữ liệu gốc hay không. Công việc đơn giản chỉ là sử dụng hàm Match cho từng giá trị một.

Giá trị tham chiếu: (lựa chọn giá trị tham chiếu mong muốn)

Tập hợp tham chiếu: (lựa chọn bảng tập hợp số liệu)

Điều kiện đối chiếu: 0 (tức là quy định Excel chỉ được tham chiếu các giá trị chính xác tuyệt đối)

Nếu giá trị trả về là 1 số thứ tự bất kỳ thì điều đó có nghĩa là giá trị mà bạn quan tâm có tồn tại trong tập hợp dữ liệu gốc. Nếu như kết quả trả về báo lỗi thì tức là không tồn tại giá trị nói trên.

Về mặt cảm quan mà nói thì đây là công dụng tốt nhất của hàm Match. Nếu một giá trị nào đó đơn giản tồn tại trong tập hợp dữ liệu đã cho thì lẽ dĩ nhiên hàm Match phải có nghĩa. Việc sử dụng hàm Match để kiểm tra giúp bạn có được kết quả trực quan và nhanh chóng nhất.

Thực tế ta còn các cách khác để xác minh sự tồn tại của một giá trị nào đó. Bạn có thể sử dụng tổ hợp phím Ctrl+F để khởi động chức năng tìm kiếm trong Excel. Hoặc bạn cũng có thể phân loại và lọc dữ liệu để tìm kiếm giá trị một cách thủ công. Tuy vậy, giải pháp nhanh nhất và hiệu quả nhất có lẽ là sử dụng hàm Match để kiểm tra, đặc biệt là khi có nhiều hơn 1 giá trị cần xác minh, như ví dụ phía trên. Chỉ cần viết công thức 1 lần, tham chiếu đầy đủ và sao chép xuống các dòng kế tiếp là xong.

GIẢI PHÁP KHÁC THAY THẾ CHO HÀM IF LỒNG NHAU

Trong cả hai ví dụ trên của hàm IF, ta đều sử dụng điều kiện đối chiếu giá trị chính xác tuyệt đối. Phần lớn khi sử dụng hàm Match phải đến 95% người dùng chỉ lựa chọn tìm kiếm giá trị chính xác mà thôi. Tuy vậy, bạn cũng có thể áp dụng hàm này đối với trường hợp điều kiện đối chiếu đó là các giá trị chính xác tương đối. Một trong số đó là việc bạn có thể sử dụng nó để tránh phải viết câu lệnh IF lồng nhau thường có bố cục khá phức tạp. Đây là danh sách các điều kiện đối chiếu mà bạn nên biết:

Loại điều kiện đối chiếu Tác vụ của Excel Điều kiện phân loại dữ liệu trong tập hợp
“1” hoặc để trống Tìm kiếm giá trị lớn nhất trong tập hợp tham chiếu có giá trị nhỏ hơn hoặc bằng giá trị tham chiếu Theo thứ tự tăng dần
“0” Tìm kiếm giá trị đầu tiên xuất hiện trong tập hợp tham chiếu có giá trị chính bằng giá trị tham chiếu Không có
“-1” Tìm kiếm giá trị nhỏ nhất trong tập hợp tham chiếu có giá trị lớn hơn hoặc bằng giá trị tham chiếu Theo thứ tự giảm dần

Đây là ví dụ cho thấy điều kiện tham chiếu quy định rõ rằng Excel chỉ được tìm các giá trị tương đối. Ví dụ những từ khóa được bôi đen dưới đây sẽ thể hiện tính tương đối của kết quả tìm kiếm:

Tìm giá trị lớn nhất trong tập hợp có giá trị nhỏ hơn hoặc bằng giá trị tham chiếu.

Giả sử ta gán 1 giá trị tham chiếu vào tập hợp các bin của biểu đồ tần suất histogram (mặc định tập hợp tham chiếu là giá trị nhỏ nhất trong số các bin của biểu đồ tần suất), khi đó ta nên sử dụng điều kiện tham chiếu tương đối. Dưới đây ta có ví dụ gán giá trị số 36 to vào tập hợp các bin của biểu đồ tần suất histogram.

Lý do vì sao hàm Match lại tỏ ra vượt trội hơn hàm If lồng nhau là bởi:

  • Có độ dài ngắn hơn và đơn giản hơn hàm If lồng
  • Dễ kiểm tra và soát lỗi hơn
  • Sử dụng hàm Match giúp bạn có thể áp dụng cho quy mô dữ liệu lớn hơn; chẳng hạn nếu bạn muốn tăng số lượng bin trong biểu đồ histogram, cấu trúc của công thức cũng không bị kéo dài hơn.

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:

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À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.