Khi tìm kiếm dữ liệu trên trang tính Excel, bạn thường sử dụng hàm nào? Có lẽ câu trả lời phổ biến nhất là hàm VLOOKUP. Vậy bạn đã biết đến các hàm tìm kiếm trong Excel khác chưa? Trong bài viết ngày hôm nay, các bạn hãy cùng Gitiho tìm hiểu về hàm MATCH và các ứng dụng mang tính đỉnh cao của hàm khi kết hợp với các hàm Excel nhé.
Giới thiệu hàm MATCH trong Excel
Cú pháp hàm MATCH trong Excel
Với chức năng hàm lấy giá trị của ô trong Excel, hàm MATCH trong Excel có cú pháp như sau:
=MATCH(lookup_value,lookup_array,[match_type])
- lookup_value: Là giá trị cần tìm kiếm trong mảng. Giá trị này có thể là giá trị văn bản, giá trị số, giá trị logic hoặc một giá trị tham chiếu ô.
- 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.
- match_type = 1 (hoặc bỏ qua): Hàm MATCH 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. Với kiểu tìm kiếm này, chúng ta phải sắp xếp dữ liệu theo thứ tự tăng dần, A-Z, FALSE và TRUE.
- match_type = 0: Hàm MATCH 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. Với kiểu tìm kiếm này, chúng ta không cần phải sắp xếp dữ liệu theo một thứ tự bắt buộc nào.
- match_type = -1: Hàm MATCH 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. Với kiểu tìm kiếm này, chúng ta phải sắp xếp dữ liệu theo thứ tự giảm dần, Z-A, TRUE và FALSE.
Một số lưu ý với hàm MATCH trong Excel
Khi sử dụng hàm tham chiếu 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 là hàm lấy giá trị của ô trong Excel, nghĩa là trả về vị trí tương đối chứa giá trị lookup_value, 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. Trong trường hợp lookup_value của bạn là ký tự “?”, bạn sẽ cần sử dụng ký tự dấu ngã “~” trước ký tự “?”.
- Nếu trong phạm vi lookup_array chứa nhiều hơn một giá trị khớp với lookup_value, hàm MATCH trong Excel luôn trả về giá trị đầu tiên.
- Nếu trong phạm vi lookup_array không chứa giá trị lookup_value, hàm tìm kiếm trong Excel trả về lỗi #N/A.
Trong trường hợp hàm tham chiếu MATCH trả về giá trị lỗi #N/A, có thể bạn sẽ cần đánh dấu lại các ô đó để tránh dẫn đến các lỗi khác khi vô tình sử dụng công thức cho ô xuất hiện lỗi. Hãy tham khảo cách sử dụng hàm NA để làm được điều này nhé.
Xem thêm: Giới thiệu về hàm NA trong Excel để hiển thị lỗi #N/A
Ví dụ cơ bản về cách sử dụng hàm MATCH
Chỉ nói lý thuyết chắc chắn sẽ là không đủ để bạn hoàn toàn nắm được cách sử dụng hàm MATCH trong Excel. Vậy thì tại phần này, hãy cùng chúng mình tìm hiểu một ví dụ đơn giản dưới đây để làm rõ phương thức hoạt động của hàm lấy giá trị của ô trong Excel nhé.
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?
Để trả lời câu hỏi trên và 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 đó:
- lookup_value = E2: Giá trị tham chiếu đến ô chứa tên học sinh cần tìm.
- lookup_array = A2:A11: Cột Học sinh (cột A) chứa tên tất cả học sinh trong bảng.
- match_type = 0: Tìm kiếm giá trị trong phạm vi lookup_array khớp hoàn toàn với giá trị lookup_value. Với kiểu tìm kiếm này, chúng ta không cần phải sắp xếp thứ tự dữ liệu trong bảng.
Như vậy, công thức hàm MATCH trong Excel đã cho chúng ta biết học sinh Nhung xếp vị trí thứ 5 trong cột Học sinh. Đó là tính năng cơ bản của hàm MATCH. Vậy thì hãy cùng khám phá một ứng dụng khác của hàm tìm kiếm trong Excel mang tên MATCH trong bài viết dưới đây nhé.
Xem thêm: Hướng dẫn sử dụng hàm MATCH tìm dòng cuối trong Excel
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
Như đã đề cập ở trên, hàm MATCH 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.
Nếu bạn còn mơ hồ về cách sử dụng hàm MATCH với các ký tự đại diện, hãy xem một ví dụ dưới đây nhé. Giả sử bạn cần tìm kiếm vị trí của một học sinh nhất định 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, 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 đó, bạn có thể đặt tham số lookup_value trong công thức hàm MATCH là tham chiếu đến ô E2 như sau:
=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. Không quan trọng rằng bạn nhập trực tiếp giá trị “Nh*” vào công thức hàm MATCH trong Excel hay nhập giá trị vào một ô khác và dẫn tham chiếu ô vào công thức hàm, kết quả nhận được sẽ vẫn là số 5.
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)
Như bạn đã thấy, cho dù bạn không thể ghi nhớ chính xác giá trị cần tìm kiếm, hàm MATCH trong Excel vẫn có thể tham chiếu bảng dữ liệu và trả về chính xác vị trí tương đối của một giá trị khớp với những gì bạn đã nhập vào tham số lookup_value. Hàm tìm kiếm trong Excel này quả thật lợi hại đúng không nào?
Xem thêm: Hướng dẫn chèn và sử dụng ký tự đặc biệt trong Excel
Hàm MATCH phân biệt chữ hoa và chữ thường
Excel đã mặc định hàm MATCH không có chức năng phân biệt chữ hoa và chữ thường. Vậy trong trường hợp bạn cần sử dụng hàm tìm kiếm trong Excel với bảng số liệu yêu cầu phân biệt 2 kiểu chữ thì phải làm thế nào? Hãy để Gitiho chỉ cho các bạn cách xử lý nhé.
Để tạo một công thức hàm MATCH phân biệt được chữ hoa và chữ thường, chúng ta sẽ cần kết hợp thêm hàm EXACT. Công thức tổng quát của chúng ta như sau:
=MATCH(TRUE,EXACT(lookup_array,lookup_value),0)
Công thức này hoạt động như sau:
- Hàm EXACT so sánh giá trị tìm kiếm lookup_value với mọi giá trị trong phạm vi lookup_array. 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 nếu trong phạm vi lookup_array không có giá trị nào khớp hoàn toàn với giá trị lookup_value.
- Hàm MATCH trong Excel 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.
Xem thêm: Hướng dẫn nhập công thức Excel vào nhiều ô bằng tổ hợp phím Ctrl Enter
Để các bạn hình dung trực quan hơn về cách sử dụng hàm MATCH trong trường hợp cần phân biệt chữ hoa và chữ thường, hãy tìm hiểu ngay một ví dụ minh họa nhé. 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.
Các mã hàng này là sự kết hợp của các ký tự chữ hoa và chữ thường khác nhau. Do đó, nếu chỉ sử dụng hàm MATCH thông thường để tìm ra vị trí của một mã hàng trong bảng thì khả năng cao chúng ta sẽ nhận về kết quả sai lệch. Để giải yêu cầu này, chúng ta sẽ tạo công thức hàm lấy giá trị của ô trong Excel kết hợp MATCH và EXACT như sau:
=MATCH(TRUE,EXACT(A2:A10,E2),0)
Trong đó: Hàm EXACT xác định yêu cầu hàm MATCH tìm kiếm giá trị trong bảng với điều kiện khớp với giá trị cần tìm lookup_value 100% cả về nội dung và hình thức chữ hoa, chữ thường.
Các phép kết hợp hàm MATCH với các hàm tìm kiếm trong Excel
Sau khi đã tìm hiểu về các ứng dụng của hàm MATCH, các bạn có tò mò về sự khác biệt của hàm này với các hàm tìm kiếm trong Excel khác? Công dụng của tất cả các hàm có điểm gì khác biệt? Trong trường hợp của mình thì nên sử dụng hàm nào? Hãy khám phá câu trả lời ngay thôi!
Hàm MATCH kết hợp hàm VLOOKUP
Nếu đã từng sử dụng hàm VLOOKUP để tham chiếu dữ liệu trang tính, chắc chắn bạn sẽ thấy đó là một hàm tìm kiếm trong Excel vô cùng đơn giản và nhanh chóng. Với cú pháp không mấy phức tạp, cách thức hoạt động không mất nhiều thời gian, hàm VLOOKUP hiện đang nắm vị trí dẫn đầu khi nói đến hàm tham chiếu phổ biến trong Excel.
Xem thêm: Hướng dẫn cách dùng hàm VLOOKUP trong Excel qua các ví dụ từ cơ bản đến nâng cao
Vậy nhưng, sau đã quen với cách tìm kiếm dữ liệu bằng hàm VLOOKUP, có thể bạn sẽ nhận ra rằng hàm VLOOKUP đơn giản là vậy, nhưng lại có rất nhiều hạn chế. Một trong những hạn chế lớn nhất của hàm này chính là việc nó không hoạt động sau khi chúng ta đã thêm hoặc bớt một số cột trong phạm vi tìm kiếm. Lí do bởi vì hàm VLOOKUP tìm kiếm dữ liệu dựa trên vị trí của cột mà bạn đã xác định từ trước. Do đó, một khi bạn thay đổi vị trí cột, hàm VLOOKUP trong Excel sẽ không thể nhận ra và tự động điều chỉnh công thức được.
Để khắc phục hạn chế này của hàm VLOOKUP, hàm MATCH chắc chắn sẽ là một trợ thủ hoàn hảo. Được thiết kế với nhiệm vụ xác định vị trí tương đối của giá trị tìm kiếm, hàm MATCH trong Excel chính là sự lựa chọn tuyệt vời cho tham số col_index_num của hàm VLOOKUP. Nói một cách đơn giản, thay vì điền vào công thức hàm VLOOKUP một vị trí cột cố định, chúng ta có thể sử dụng hàm MATCH – hàm lấy giá trị của ô, để xác định vị trí hiện tại của cột cần tham chiếu. Như vậy, hàm MATCH kết hợp VLOOKUP sẽ giải quyết hoàn toàn rắc rối với việc sử dụng hàm tìm kiếm trong Excel sau khi thay đổi cấu trúc các cột trên trang tính.
Hãy tìm hiểu một ví dụ về bảng điểm học sinh. 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)
Tiếp theo, chúng ta xóa cột điểm Toán khỏi bảng. Như bạn đã biết, thao tác này chắc chắn sẽ làm thay đổi cấu trúc các cột trên trang tính Excel. Do đó, bạn sẽ thấy lỗi #REF! hiện lên tại ô F2. Lỗi này thông báo cho chúng ta rằng tham chiếu không hợp lệ, bắt nguồn từ việc công thức VLOOKUP chỉ định đến 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 đó.
Câu hỏi đặt ra là: Làm thế nào để xử lý vấn đề này? Hãy áp dụng công thức hàm MATCH kết hợp VLOOKUP chúng mình đã giới thiệu ở phía trên. Trước tiên, chúng ta có công thức hàm MATCH trong trường hợp này như sau:
=MATCH(D3,A1:C1,0)
Trong đó:
- lookup_value = D3: Giá trị cần tìm đặt trong ô D3, là tên chính xác của cột Điểm Văn.
- lookup_array = A1:C1: Phạm vi tìm kiếm giá trị lookup_value là 3 tiêu đề của bảng điểm.
- match_type = 0: Tìm kiếm giá trị khớp hoàn toàn với lookup_value.
Như vậy, hàm MATCH sẽ tìm kiếm và trả về chính xác vị trí của cột Điểm Văn, bất kể bạn thêm hay bớt bao nhiêu cột trong trang tính Excel. Chúng ta lồng công thức hàm lấy giá trị của ô này vào tham số col_index_num để xác định vị trí cột trong công thức hàm VLOOKUP:
=VLOOKUP(E2,A1:C11,MATCH(D3,A1:C1,0),FALSE)
Hãy thử kiểm tra kết quả của công thức hàm MATCH kết hợp VLOOKUP này nhé.
Như bạn đã thấy, không hề có sự khác biệt hay lỗi Excel nào xảy ra khi chúng ta áp dụng phép kết hợp hàm MATCH và VLOOKUP để tạo một công thức hàm tìm kiếm trong Excel. Chính sự linh hoạt lợi hại của hàm tham chiếu MATCH đã nâng công dụng của hàm VLOOKUP lên một tầm cao mới.
Xem thêm: Hướng dẫn cách dùng hàm VLOOKUP nâng cao
Hàm MATCH kết hợp hàm HLOOKUP
Tương tự như hàm VLOOKUP tìm kiếm ngang, hàm HLOOKUP tìm kiếm dọc cũng có một nhược điểm khó ưa: Hàm 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ậy là lại một lần nữa, chúng ta cần đến sự linh hoạt của hàm MATCH để kết hợp với một hàm tìm kiếm trong Excel.
Nếu như hàm MATCH trong Excel đã xuất sắc hoàn thành vai trò tham số col_index_num tại công thức hàm VLOOKUP, thì đối với hàm HLOOKUP, cách sử dụng hàm MATCH hợp lý nhất là đưa vào tham số row_index_col. Nguyên tắc có thể được hiểu như phần giải thích của chúng mình về trường hợp hàm VLOOKUP: 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ẫn tiếp tục với ví dụ về bảng điểm trong phần hàm MATCH kết hợp VLOOKUP, chúng ta sẽ tìm hiểu công thức hàm tìm kiếm trong Excel hữu hiệu nhất khi tham chiếu dữ liệu trong bảng ngang.
Đến đây, có thể bạn sẽ băn khoăn cách chuyển bảng từ dọc thành ngang nhanh chóng mà không cần nhập liệu từ đầu. Mẹo của chúng mình đối với thao tác này chính là copy và sử dụng tính năng Paste Special của Excel. Tham khảo bài viết dưới đây để tìm hiểu chi tiết nhé.
Xem thêm: Cách sử dụng Paste Special trong bài thi MOS Excel hiệu quả nhất
Quay trở lại với ví dụ về hàm VLOOKUP, chúng ta có công thức hàm cơ bản như sau:
=VLOOKUP(B5, B1:K3,3,FALSE)
Hàm VLOOKUP hoạt động trơn tru, không hề xảy ra vấn đề gì cho đến khi ta xóa đi dòng Điểm Toán. Tương tụ như với hàm VLOOKUP, 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 trong Excel xem sao nhé!
=MATCH(A6,A1:A3,0)
Trong đó:
- lookup_value = A6: Giá trị cần tìm đặt trong ô A6, là tên chính xác của hàng Điểm Văn.
- lookup_array = A1:A3: Phạm vi tìm kiếm giá trị lookup_value là 3 tiêu đề của bảng điểm.
- match_type = 0: Tìm kiếm giá trị khớp hoàn toàn với lookup_value.
Ghép công thức hàm MATCH trên vào tham số row_index_num của hàm HLOOKUP, chúng ta có công thức hàm tìm kiếm trong Excel như sau:
=HLOOKUP(B5,B1:K3,MATCH(A6,A1:A3,0),FALSE)
Hàm MATCH kết hợp hàm INDEX
Chúng mình sẽ liệt kê những lý do bạn nên sử dụng hàm INDEX MATCH thay vì hàm LOOKUP. Hàm INDEX trả về giá trị tại giao điểm một hàng và một cột. Hàm tìm kiếm trong Excel này có 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é.
Tìm kiếm từ phải sang trái
Nếu đã quen với các hàm LOOKUP, chắc chắn bạn sẽ nhận ra các hàm tìm kiếm trong Excel này 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. Bạn sẽ không còn phải lo lắng về nhược điểm này của hàm LOOKUP nếu bạn sử dụng hàm INDEX MATCH.
Giả sử chúng ta có một bảng thông tin như sau:
Dựa trên dữ liệu của bảng tổng hợp này, 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, rất tiếc khi phải thông báo với bạn rằng hàm LOOKUP không thể giúp gì trong trường hợp này. Thay vào đó, hàm MATCH trong Excel và hàm INDEX sẽ trở thành trợ thủ đắc lực cho bạn.
Hãy điền vào ô F3 công thức hàm INDEX MATCH như sau:
=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.
Như vậy, chúng ta đã thành công tìm kiếm dữ liệu bằng cách sử dụng hàm INDEX và MATCH. Không có gì khó khăn phải không nào!
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
Như đã đề cập trong phần trên, các hàm LOOKUP sẽ dừng hoạt động nếu bạn thay đổi cấu trúc trang tính Excel của mình. 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ới hàm tìm kiếm trong Excel dạng này, cấu trúc trang tính Excel sẽ không làm ảnh hưởng đến kết quả công thức tham chiếu.
Hãy thử áp dụng công thức hàm MATCH và hàm INDEX vào ví dụ về bảng điểm chúng ta đã tìm hiểu với ví dụ cách sử dụng hàm MATCH với các hàm LOOKUP nhé.
Công thức hàm INDEX và MATCH được sử dụng như sau:
=INDEX(C2:C11,MATCH(F2,A2:A11,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 học sinh 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ọc sinh (cột A), tại vị trí tương ứng với kết quả hàm MATCH trong Excel.
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. Điều này chứng tỏ tính linh hoạt đỉnh cao của công thức hàm tìm kiếm trong Excel khi chúng ta kết hợp hàm MATCH và hàm INDEX.
Xem thêm: Hướng dẫn cách lập sổ điểm theo lớp và môn cho giáo viên trong Excel
Tìm kiếm theo nhiều điều kiện
So với các công thức hàm INDEX MATCH chúng ta đã tìm hiểu, 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. Tuy nhiên, đừng vì vậy mà từ bỏ bạn nhé! Bởi đây là một trường hợp rất phổ biến mà rất có thể bạn sẽ gặp phải khi tham chiếu và xử lý dữ liệu trên Excel đấy.
Để dễ dàng hiểu được cách sử dụng hàm MATCH trong Excel và hàm INDEX với yêu cầu tìm kiếm theo nhiều điều kiện, hãy xem ngay ví dụ dưới đây nhé.
Giờ thì bạn hãy để ý trong các trường hợp hàm INDEX và MATCH trên, chỉ tồn tại một điều kiện duy nhất để tham chiếu dữ liệu. Do đó, chúng ta chỉ sử dụng một công thức hàm MATCH với mục đích xác định row_num, tức vị trí hàng của giá trị cần tìm.
Với ví dụ này, 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. Về bản chất vấn đề, chúng ta cần sử dụng hàm INDEX để xác định giá trị tại giao điểm của hàng và cột. Để làm được điều này, hãy tạo 2 công thức hàm MATCH đóng vai trò 2 hàm trả về giá trị của ô để lần lượt điền vào các tham số row_num và col_num của hàm INDEX.
- Bước 1: Tạo công thức hàm MATCH trong Excel xác định vị trí Mã công ty trong bảng.
=MATCH(H3,A3:A6,0)
Công thức này sẽ xác định tham số row_num trong hàm INDEX bằng cách trả về vị trí giá trị trong bảng khớp hoàn toàn với giá trị Mã công ty đặt tại ô H3.
- 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)
Công thức hàm MATCH đặt tại tham số col_num của hàm INDEX sẽ xác định vị trí cột chứa giá trị bằng chính xác giá trị Mã hàng đã điền trong ô H2.
- Bước 3: Hoàn thành công thức hàm INDEX trả về giá trị cần tìm
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 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, đó chính 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.
Hàm tìm kiếm trong Excel như trong hình có vẻ hơi phức tạp một chút, nhưng hãy ghi nhớ kiến thức về cách sử dụng hàm INDEX và MATCH để tìm kiếm nhiều điều kiện bạn nhé!
Xem thêm: Hướng dẫn cách dùng hàm Match tìm kiếm nhiều điều kiện
Tìm kiếm kết hợp tính toán
Một điểm đặc biệt với hàm INDEX MATCH chính là 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.
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.
Để 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.
Hoàn toàn tương tự, để xác định học sinh có số điểm tổng kết thấp nhất trong bảng, chúng ta thiết lập công thức hàm tìm kiếm trong Excel dưới đây:
=INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0))
Khác với cách xác định các học sinh có điểm tổng kết cao và thấ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 trước khi áp dụng công thức hàm MATCH kết hợp INDEX để tìm học sinh có số điểm gần với trung bình lớp nhất.
Công thức được sử dụng trong trường hợp này là:
=INDEX(A2:A11,MATCH(AVERAGE(B2:B11),B2:B11,1))
Như bạn thấy, chúng ta sử dụng match_type = 1 đối với hàm MATCH trong Excel. Đâ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%.
Xem thêm: Giới thiệu các hàm trong Excel và các ví dụ minh họa dễ hiểu
Ngoài các công thức trên, phép kết hợp hàm MATCH và hàm INDEX còn phát huy hiệu quả tuyệt vời nếu bạn đang xử lý một chuỗi dữ liệu phức tạp với hàng ngàn con số và các công thức tính toán và logic. So với công thức hàm LOOKUP, chắc chắn việc sử dụng hàm INDEX và MATCH sẽ đẩy nhanh tốc độ tìm kiếm của bạn hơn nhiều lần.
Trong trường hợp file Excel của bạn chứa một kho dữ liệu ngổn ngang dẫn đến việc chạm tới giới hạn 255 ký tự của hàm VLOOKUP hay HLOOKUP, hãy chuyển ngay sang dùng hàm INDEX MATCH nhé. Đây sẽ là giải pháp duy nhất khả thi cho chuỗi dữ liệu Excel khổng lồ của bạn đấy.
Với tất cả các lợi ích không thể chối cãi của hàm INDEX và MATCH, thật không sai khi nói rằng đây chính là một trong những phép kết hợp mạnh mẽ và hiệu quả nhất giữa các hàm tìm kiếm trong Excel tính đến thời điểm hiện tại. Các bạn có đồng ý với nhận định này không?
Hàm MATCH kết hợp hàm ISNA
Khi kết hợp với hàm ISNA, hàm MATCH lại đóng một vai trò mới: Thám tử truy vết điểm trùng khớp và khác biệt giữa dữ liệu các cột trong bảng. Đầu tiên, chúng ta hãy tìm hiểu đôi điều về hàm ISNA trong Excel nhé.
Hàm ISNA giúp người dùng trả lời câu hỏi: Giá trị này có chứa lỗi #N/A không?. Cú pháp hàm được thể hiện vô cùng đơn giản như sau:
=ISNA(value)
Trong đó: value là giá trị cần xác định kết quả. Giá trị này có thể là giá trị văn bản, giá trị số, giá trị ngày tháng hay một biểu thức logic, tham chiếu ô tính Excel.
Xem thêm: Hướng dẫn cách sử dụng hàm ISNA trong Excel để tránh lỗi #N/A
Giờ thì bạn đã nắm được kiến thức cơ bản về hàm ISNA rồi. Cùng tiếp tục với cách phân biệt dữ liệu 2 cột nhé. Giả sử bạn cần đá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 dưới đây:
=IF(ISNA(MATCH(giá trị đầu tiên trong cột 1, cột 2, 0)), “Không có trong cột 1”, “”)
- Hàm MATCH trong Excel lần lượt so sánh từng giá trị ở cột 2 với tất cả các giá trị ở cột 1. Nếu giá trị tại cột 2 được tìm thấy trong cột 1, hàm MATCH sẽ trả về vị trí tương đối của giá trị đó. Ngược lại, nếu hàm cho rằng một giá trị tại cột 2 không có ở cột 1, hàm sẽ trả về lỗi #N/A.
- Hàm ISNA kiểm tra kết quả hàm MATCH có phải lỗi #N?A hay không. Nếu kết quả lỗi #N/A của hàm MATCH trong Excel vừa trả về là chính xác, tức là một giá trị ở cột 2 không tồn tại ở cột 1, hàm ISNA sẽ trả về TRUE. Ngược lại, nếu hàm tìm thấy giá trị đó trong cột 1, kết quả trả về là FALSE. Các bạn hãy lưu ý kỹ phần này để tránh nhầm lẫn nhé.
- Nếu giá trị đang tham chiếu ở cột 2 không tồn tại ở cột 1, hàm IF sẽ trả về kết quả “Không có trong cột 1”. Ngược lại, nếu giá trị đó xuất hiện ở cả 2 cột, kết quả nhận được là giá trị rỗng. Bạn hoàn toàn có thể thay đổi các giá trị hiển thị này tùy theo ý thích của mình.
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:
Chúng ta sẽ áp dụng công thức hàm tìm kiếm trong Excel vừa học để phân biệt các dữ liệu khác nhau giữa 2 cột, cụ thể là đối chiếu toàn bộ giá trị cột 2 với giá trị tương ứng trong cột 1.
=IF(ISNA(MATCH(B2,A:A,0)),”Không có trong cột 1″,””)
Sau khi áp dụng AutoFill cho tất cả các hàng dữ liệu trong bả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. Như vậy, chúng ta không phải mất thời gian so sánh sự khác biệt này bằng mắt nữa.
Với ví dụ này, chúng ta mới dừng lại ở mức độ phân biệt nội dung các cột, chứ chưa tính đến dạng chữ. Vậy trong trường hợp cần phân biệt chữ hoa và chữ thường, chúng ta sẽ làm thế nào? Hãy áp dụng thêm hàm EXACT vào công thức hàm MATCH như chúng mình đã đề cập trong các phần đầu bài nhé.
Lúc này, công thức hàm tìm kiếm trong Excel dài hơn một chút như sau:
=IF(ISNA(MATCH(TRUE,EXACT(A:A,B2),0)),“Không có trong cột 1”, “”)
Việc phát hiện các điểm khác biệt giữa 2 cột dữ liệu này dường như rất khó khăn nếu ta chỉ dùng mắt. Thay vào đó, tại sao không dùng đến công thức hàm logic chúng ta vừa tìm hiểu nhỉ?
=IF(ISNA(MATCH(TRUE,EXACT(A:A,B2),0)),”Không có trong cột 1″,””)
Công thức này sẽ xác định các giá trị tồn tại ở cột 2 mà không có trong cột 1, tương tự như với ví dụ trên. Hãy xem hình dưới đây và cùng kiểm tra liệu kết quả có chính xác như chúng ta mong đợi không nhé.
Như vậy, các giá trị duy nhất tại cột 2 lần lượt là “EXG01”, “exg01” và “ExG02”. Các bạn có thể kiểm tra lại kết quả này để đề phòng trường hợp xảy ra sai sót đáng tiếc nhé. Đừng quên xem lại kỹ công thức hàm của mình trước khi chạy. Bạn sẽ không muốn lục lại từng dấu phẩy trong công thức của mình khi phát hiện kết quả trả về xảy ra sai lệch đâu.
Bên cạnh cách sử dụng hàm MATCH kết hợp hàm ISNA để xác định các giá trị khác biệt giữa các cột trong bảng, chúng ta còn có một phương pháp khác với hàm ROWS và COLUMNS. Không chỉ dừng lại ở đó, 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 sử dụng hàm so sánh trong Excel kiểm tra sai lệch dữ liệu
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. Lợi thế của hàm tham chiếu MATCH chính là sự kết hợp linh hoạt và hiệu quả của nó với các hàm tìm kiếm trong Excel. Hy vọng qua bài viết của chúng mình, bạn đã nắm được cách sử dụng hàm MATCH thông minh để xử lý các trường hợp cụ thể.
Bên cạnh cách sử dụng hàm MATCH, còn rất nhiều các hàm Excel khác đang chờ bạn khám phá. Hãy tham khảo các bài viết trên blog Gitiho để tích lũy kiến thức về kho tàng công thức vô tận của Excel bạn nhé. Nếu bạn đang tìm kiếm một khóa học Excel, cùng tham gia khóa học Tuyệt đỉnh Excel với chúng mình ngay thôi!
Gitiho xin cảm ơn bạn đọc và chúc bạn áp dụng kiến thức hàm MATCH thành công!
