Đánh giá lequocthai.com:
Hàm MATCH đặc biệt hữu ích khi làm việc với bộ dữ liệu lớn, vì nó cho phép xác định nhanh chóng vị trí dữ liệu tương đối, tránh việc phải dò tìm thủ công toàn bộ dữ liệu.
Cú pháp
Cú pháp của hàm MATCH bao gồm ba đối số, trong đó một đối số là tùy chọn.
=MATCH(lookup_value, lookup_array, [match_type])Đối số:
- lookup_value – Đối số bắt buộc, nhận giá trị cần tìm trong một phạm vi ô hoặc mảng cho trước. Giá trị này có thể là số, văn bản, giá trị logic hoặc một ô tham chiếu.
- lookup_array – Đối số bắt buộc, chứa phạm vi ô hoặc mảng một chiều (có thể là dọc hoặc ngang) mà chúng ta muốn tìm lookup_value. Đối số này cần được sắp xếp tăng dần hoặc giảm dần tùy theo match_type được sử dụng.
- match_type – Đối số tùy chọn, cho phép xác định cách khớp. Giá trị chấp nhận được là -1, 0 hoặc 1; nếu để trống, mặc định là 1.
- Khi match_type = 1, hàm MATCH tìm giá trị lớn nhất ≤ lookup_value trong một dải đã sắp xếp tăng dần.
- Khi match_type = 0, hàm tìm khớp chính xác.
- Khi match_type = -1, hàm MATCH tìm giá trị nhỏ nhất ≥ lookup_value trong một dải đã sắp xếp giảm dần.
Hiểu các khả năng trên, chúng ta sẽ xem qua một số đặc điểm quan trọng của hàm MATCH trước khi chuyển sang ví dụ thực tế.
Đặc điểm quan trọng của hàm MATCH
- Hàm MATCH không phân biệt chữ hoa‑chữ thường.
- Khi không tìm thấy giá trị, hàm trả về lỗi #N/A.
- Hàm chỉ làm việc với văn bản có độ dài tối đa 255 ký tự.
- Khi có giá trị trùng lặp, hàm trả về vị trí xuất hiện đầu tiên.
- Khi match_type = 0 và lookup_value là chuỗi, chúng ta có thể sử dụng ký tự đại diện: dấu hỏi (?) đại diện cho một ký tự bất kỳ, dấu sao (*) đại diện cho một chuỗi ký tự bất kỳ trước hoặc sau. Để tìm dấu hỏi hoặc dấu sao thực sự, đặt trước chúng ký tự gạch ngược (~).
Ví dụ về hàm MATCH
Hãy bắt đầu bằng cách hiểu các đối số và cách chúng ảnh hưởng đến hàm MATCH. Ở đây chúng tôi sẽ sử dụng một bộ dữ liệu mẫu và áp dụng các giá trị đầu vào khác nhau cho từng đối số để khám phá sâu hơn cách hoạt động của hàm.
Trong hai ví dụ đầu, match_type được đặt là 1. Trong trường hợp đầu tiên, chúng ta tìm giá trị “110” trong mảng B1:F1. Vì lookup_value không tồn tại trong bộ dữ liệu và match_type = 1, công thức MATCH sẽ tìm giá trị lớn nhất ≤ 110 trong mảng. Kết quả trả về là 5 vì mảng đã được sắp xếp tăng dần.
Trong ví dụ tiếp theo, chúng ta tìm giá trị “100” với các đối số còn lại giữ nguyên. Vì không có giá trị nào bằng hoặc nhỏ hơn 100, hàm MATCH trả về lỗi #N/A.
Ví dụ thứ ba là trường hợp lý tưởng: chúng ta tìm khớp chính xác cho giá trị “103” trong mảng. Hàm MATCH xác định được vị trí và trả về vị trí của lookup_value.
Hai ví dụ cuối cùng khám phá hành vi khi match_type = -1. Khi đó, hàm MATCH tìm giá trị nhỏ nhất ≥ lookup_value. Vì không có giá trị nào lớn hơn 1000, hàm trả về lỗi. Khi lookup_value = 990, hàm trả về 5 cho giá trị “995” – là giá trị nhỏ nhất lớn hơn 990.
Ví dụ 1 – Sử dụng đơn giản hàm MATCH
Giả sử bạn sở hữu một cửa hàng nội thất bán các sản phẩm như kệ sách, ghế sofa, v.v. Bạn có một bộ dữ liệu chứa các cột ID sản phẩm, tên sản phẩm và tổng doanh thu của từng sản phẩm.
Bạn muốn xác định thứ hạng của sản phẩm dựa trên doanh thu bằng ID sản phẩm.
Bạn có thể để ô E1 cho người dùng nhập ID sản phẩm cần tìm, sau đó dùng hàm MATCH để xác định vị trí (hàng) của ID đó. Vì cần khớp chính xác, công thức sẽ là:
=MATCH(E1,A2:A11,0)
Điều này cực kỳ hữu ích khi bộ dữ liệu lớn. Vì dữ liệu hiện đang được sắp xếp từ doanh thu cao đến thấp, kết quả của hàm MATCH cũng cung cấp thứ hạng hoặc mức độ phổ biến của sản phẩm dựa trên doanh thu.
Thêm nữa, các ID sản phẩm được tạo sao cho ba ký tự đầu của ID trùng khớp với ba ký tự đầu của tên sản phẩm. Nếu người dùng không nhớ toàn bộ ID, chúng ta có thể tận dụng ký tự đại diện mà hàm MATCH hỗ trợ. Tiếp theo, chúng tôi sẽ hướng dẫn cách thực hiện.
Ví dụ 2 – Sử dụng ký tự đại diện với hàm MATCH
Để đơn giản hoá trải nghiệm người dùng, chúng ta chỉ yêu cầu họ nhập ba ký tự đầu của tên sản phẩm. Sau đó, chúng ta chỉnh sửa đối số lookup_value bằng cách nối ba ký tự nhập vào với ký tự đại diện “*”. Điều này khiến hàm MATCH tìm bất kỳ giá trị nào bắt đầu bằng chuỗi người dùng nhập và tiếp theo là bất kỳ số ký tự nào.
Công thức sẽ là:
=MATCH(E1&"*",A2:A11,0)
Dấu sao (*) đại diện cho các ký tự còn lại của mã sản phẩm “SOF234PQR” sau các ký tự đã nhập ở E1. Như đã lưu ý ở trên, hàm MATCH không phân biệt chữ hoa‑chữ thường. Trong ví dụ tiếp theo, chúng ta sẽ khám phá cách làm cho hàm MATCH phân biệt chữ hoa‑chữ thường.
Ví dụ 3 – Làm cho hàm MATCH phân biệt chữ hoa‑chữ thường
Giả sử bạn là một quản lý marketing đang lên kế hoạch cho chiến dịch quảng cáo mới và muốn hợp tác với các influencer trên mạng xã hội. Bạn có một bộ dữ liệu chứa tên, tên người dùng và số lượng người theo dõi của các influencer tiềm năng.
Bạn muốn xác định vị trí của một influencer dựa trên tên người dùng.
Vì các tên người dùng chứa cả chữ hoa và chữ thường, hàm MATCH đơn thuần không đủ. Thay vào đó, chúng ta sẽ kết hợp hàm EXACT (so sánh chính xác có phân biệt chữ hoa‑chữ thường) với MATCH.
Công thức EXACT sẽ trả về một mảng các giá trị TRUE/FALSE cho mỗi ô trong dải B2:B15, dựa trên việc lookup_value (ô E1) có khớp chính xác (phân biệt chữ hoa‑chữ thường) hay không:
=EXACT(E1,B2:B15)
Sau đó, chúng ta dùng MATCH để tìm vị trí của giá trị TRUE trong mảng trên:
=MATCH(TRUE,EXACT(E1,B2:B15),0)
Như vậy, hàm EXACT giúp xác định có khớp hoàn toàn hay không, còn hàm MATCH cho biết vị trí của khớp đó trong danh sách.
Bạn có thể đã từng so sánh hai cột để tìm ra các mục trùng lặp và khác biệt. Hàm MATCH là một cách hiệu quả để thực hiện công việc này. Hãy cùng xem ví dụ tiếp theo.
Ví dụ 4 – So sánh hai danh sách bằng hàm MATCH
Trong kịch bản này, công ty tổ chức một chương trình đào tạo kỹ thuật mở cho tất cả các phòng ban. Chúng ta có hai danh sách địa chỉ email: danh sách 1 là email của đội kỹ thuật, danh sách 2 là email của những người được mời tham dự chương trình đặc biệt.
Mục tiêu là xác định email nào trong danh sách mời thuộc đội kỹ thuật và email nào không.
Bước đầu, dùng MATCH để so sánh mỗi ô trong cột B với toàn bộ dữ liệu cột A:
=MATCH(B2,A:A,0)Kéo công thức xuống đến cuối dữ liệu.
Tiếp theo, kiểm tra xem giá trị trả về của MATCH có phải là lỗi #N/A hay không. Nếu có lỗi, hàm ISNA sẽ trả về TRUE; ngược lại FALSE:
=ISNA(MATCH(B2,A:A,0))
Cuối cùng, đặt công thức trên vào hàm IF để hiển thị “Not in Tech Team” (Không thuộc đội kỹ thuật) hoặc “Member of the Tech Team” (Thành viên đội kỹ thuật):
=IF(ISNA(MATCH(B2,A:A,0)), "Not in Tech Team", "Member of the Tech Team")
Nếu các cột cần so sánh có giá trị phân biệt chữ hoa‑chữ thường, bạn có thể kết hợp logic ở ví dụ trước để cải thiện công thức.
Ví dụ 5 – Kết hợp VLOOKUP với hàm MATCH
Giả sử bạn làm trong phòng Nhân sự và có một bảng Excel chứa thông tin nhân viên: tên, phòng ban và mức lương. Bạn muốn tra cứu mức lương của một nhân viên dựa trên tên.
Cách đơn giản nhất là dùng hàm VLOOKUP:
=VLOOKUP(G1,$A$2:$D$11,3,FALSE)
Tuy nhiên, nếu bạn xóa cột B (cột “Department”), công thức trên sẽ tự động điều chỉnh table_array nhưng vẫn trả về cột thứ ba, tức là “Joining Date”.
Đây là hạn chế của VLOOKUP khi thêm hoặc xóa cột. Để khắc phục, chúng ta dùng MATCH để xác định chỉ số cột một cách động.
Giả sử chúng ta muốn lấy giá trị trong cột “Salary (USD)”. Đầu tiên, dùng MATCH để tìm vị trí của tiêu đề này trong hàng 1:
=MATCH("Salary (USD)",A1:D1,0)
Sau đó, đưa kết quả này vào VLOOKUP:
=VLOOKUP(G1,$A$2:$D$11,MATCH("Salary (USD)",A1:D1,0),FALSE)
Nhờ vậy, dù bạn thêm hay xóa cột, công thức vẫn trả về mức lương chính xác vì cột được xác định dựa trên tiêu đề.
Bạn cũng có thể áp dụng cùng cách tiếp cận cho HLOOKUP.
Ngoài ra, có thể dùng MATCH kết hợp INDEX để trả về giá trị tương ứng mà không phụ thuộc vào vị trí cột cố định:
=INDEX(C2:C11,MATCH(G1,A2:A11,0))Trong ví dụ này, MATCH tìm vị trí của “G1” trong dải A2:A11 (kết quả là 2) và INDEX trả về giá trị ở hàng 2 của dải C2:C11 (55000).
Hãy tự thực hành thêm để khám phá cách hàm MATCH phù hợp với công việc và dự án của bạn. Khi bạn nắm bắt được nhiều ứng dụng thú vị hơn, chúng tôi sẽ tiếp tục giới thiệu các hàm Excel khác để bổ sung vào bộ công cụ của bạn. Hãy chờ đón những công cụ Excel đa dạng hơn trong thời gian tới.
