VLOOKUP đã từng là hàm tìm kiếm dữ liệu quen thuộc, nhưng những giới hạn cố hữu về hướng tìm kiếm và cú pháp rườm rà thường gây ra không ít phiền toái. Đối với những người thường xuyên làm việc với dữ liệu trên Excel, việc phải sắp xếp lại bảng tính chỉ để phù hợp với yêu cầu của VLOOKUP là một sự lãng phí thời gian không đáng có. May mắn thay, Excel đã mang đến một giải pháp mạnh mẽ hơn rất nhiều: hàm XMATCH, giúp bạn thực hiện các thao tác tìm kiếm linh hoạt theo mọi hướng và kiểm soát kết quả khớp chính xác hơn.
Bài viết này sẽ đi sâu vào những ưu điểm vượt trội của XMATCH, cách hàm này giải quyết các “nỗi đau” của VLOOKUP, và cách kết hợp nó với hàm INDEX để tạo ra một bộ đôi tìm kiếm dữ liệu không thể mạnh mẽ hơn. Hãy cùng khám phá vì sao XMATCH đang dần trở thành công cụ không thể thiếu trong quy trình làm việc trên Excel của nhiều chuyên gia.
XMATCH Hoạt Động Linh Hoạt Mọi Hướng, Phá Vỡ Giới Hạn Của VLOOKUP
Vấn đề lớn nhất với VLOOKUP là yêu cầu cứng nhắc về hướng tìm kiếm từ trái sang phải. Nếu cột chứa giá trị bạn muốn tìm kiếm (lookup column) không phải là cột ngoài cùng bên trái trong vùng dữ liệu của bạn, bạn sẽ gặp khó khăn. Bạn buộc phải sắp xếp lại dữ liệu hoặc tìm một giải pháp thay thế phức tạp, tốn thời gian không cần thiết.
Hàm XMATCH của Excel đã loại bỏ hoàn toàn rào cản này. Không giống như cấu trúc cứng nhắc của VLOOKUP, XMATCH có khả năng tìm kiếm trong bất kỳ mảng nào và theo bất kỳ hướng nào bạn muốn. Bạn có thể dễ dàng tìm kiếm dữ liệu nằm ở bên trái cột tìm kiếm của mình. Hơn nữa, XMATCH còn xử lý linh hoạt cả việc tìm kiếm theo chiều dọc xuống một cột hoặc theo chiều ngang qua một hàng, mà không gặp bất kỳ trở ngại nào.
Cú pháp cơ bản của hàm XMATCH như sau:
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Hãy cùng phân tích các tham số này để hiểu rõ hơn về cách XMATCH hoạt động:
- lookup_value: Giá trị bạn muốn tìm kiếm.
- lookup_array: Phạm vi (cột hoặc hàng) mà bạn muốn tìm kiếm giá trị đó.
- match_mode: Cách thức hàm tìm kiếm khớp chính xác (0 cho khớp chính xác, -1 cho khớp chính xác hoặc giá trị nhỏ nhất tiếp theo, 1 cho khớp chính xác hoặc giá trị lớn nhất tiếp theo).
- search_mode: Hướng tìm kiếm (1 cho tìm từ đầu đến cuối, -1 cho tìm từ cuối đến đầu, 2 cho tìm kiếm nhị phân).
Hãy xem xét một ví dụ thực tế từ cơ sở dữ liệu nhân viên. Giả sử bạn cần tìm phòng ban của “Kristen Tate”, nhưng tên nhân viên nằm ở cột D trong khi phòng ban nằm ở cột B. Với VLOOKUP, bạn sẽ buộc phải sắp xếp lại dữ liệu vì không thể tìm kiếm ngược từ phải sang trái. Tuy nhiên, bạn có thể sử dụng hàm XMATCH để trả về vị trí của “Kristen Tate” trong cột tên nhân viên như trong công thức sau:
=XMATCH("Kristen Tate", D:D, 0)Điều này đã thay đổi đáng kể quy trình làm việc. Bạn không còn phải đếm số thứ tự cột như khi sử dụng VLOOKUP, nơi mỗi khi bạn thêm một cột mới vào dữ liệu, các công thức của bạn có thể bị lỗi do chỉ số cột thay đổi.
Vị trí nhân viên Kristen Tate trong cột D bằng hàm XMATCH Excel
XMATCH Mang Đến Kiểm Soát Khớp Dữ Liệu Tối Ưu Với Tham Số Mở Rộng
Các tùy chọn khớp của VLOOKUP khá hạn chế, chỉ bao gồm khớp chính xác (exact match) hoặc khớp tương đối (approximate match). Nếu dữ liệu của bạn không hoàn toàn sạch sẽ hoặc có định dạng không đồng nhất, bạn sẽ phải mất thời gian để dọn dẹp bảng tính Excel trước khi có thể bắt đầu quá trình tìm kiếm.
XMATCH giúp việc này trở nên dễ dàng hơn nhờ tham số match_mode linh hoạt. Bạn có thể đặt nó bằng 0 cho các khớp chính xác, tương tự như tham số FALSE của VLOOKUP. Nhưng điều thú vị nằm ở đây: bạn có thể sử dụng -1 để tìm kiếm khớp chính xác hoặc giá trị nhỏ nhất tiếp theo, và 1 cho khớp chính xác hoặc giá trị lớn nhất tiếp theo.
Hãy xem xét ví dụ về mức lương trong tập dữ liệu nhân viên. Để tìm nhân viên có mức lương gần nhất nhưng không vượt quá 75.000 USD, bạn sẽ sử dụng công thức:
=XMATCH(75000, H:H, -1)Công thức này sẽ trả về vị trí của mức lương cao nhất từ cột H mà không vượt quá giá trị mục tiêu của bạn – một điều mà VLOOKUP rất khó xử lý trừ khi dữ liệu của bạn đã được sắp xếp hoàn hảo.
Tìm vị trí lương nhân viên gần nhất không quá 75000 USD với XMATCH
Tham số search_mode bổ sung thêm một lớp kiểm soát nữa. Trong khi 1 tìm kiếm từ đầu đến cuối (mặc định), -1 tìm kiếm từ cuối đến đầu. Tham số này đặc biệt hữu ích khi bạn có các giá trị trùng lặp và cần tìm lần xuất hiện gần đây nhất hoặc cuối cùng.
Ví dụ, nếu “John Smith” xuất hiện nhiều lần trong cột D của một tập dữ liệu, chúng ta có thể sử dụng công thức sau để tìm lần xuất hiện cuối cùng của anh ấy:
=XMATCH("John Smith", D:D, 0, -1)Hiểu rõ các tham số này giúp bạn tiếp cận việc tìm kiếm dữ liệu hiệu quả hơn. Mức độ kiểm soát này đồng nghĩa với việc ít cột phụ hơn và ít thao tác dữ liệu hơn. Các công thức của bạn trở nên mạnh mẽ hơn và bảng tính của bạn được giữ sạch sẽ hơn.
Kết Hợp XMATCH Với INDEX: Bộ Đôi Hoàn Hảo Để Tìm Kiếm Dữ Liệu Mạnh Mẽ
XMATCH trở nên tiện dụng hơn nữa khi bạn kết hợp nó với hàm INDEX. Trong khi XMATCH tìm kiếm vị trí của một giá trị, INDEX sẽ truy xuất giá trị thực tế từ vị trí đó. Đây là một trong những hàm hữu ích để tìm dữ liệu nhanh chóng trong Excel, nhưng khi kết hợp với nhau, chúng tạo ra một bộ đôi tìm kiếm linh hoạt và mạnh mẽ hơn nhiều.
Đây là cú pháp cơ bản khi bạn kết hợp cả hai hàm:
=INDEX(return_array, XMATCH(lookup_value, lookup_array, [match_mode]))Sự kết hợp này loại bỏ hoàn toàn cơn ác mộng đếm cột của VLOOKUP. Thay vì phải nhớ rằng lương là cột thứ tám, bạn chỉ cần chỉ định trực tiếp cột chứa lương. Do đó, bạn sẽ không gặp phải tình trạng công thức bị lỗi khi thêm hoặc xóa cột trong bảng dữ liệu.
Giả sử bạn cần tìm phòng ban của Kristen Tate từ dữ liệu nhân viên bằng cách sử dụng INDEX và XMATCH:
=INDEX(R:R, XMATCH("Kristen Tate", D:D, 0))Công thức trên được đọc một cách tự nhiên và trả về giá trị từ cột R tại vị trí mà “Kristen Tate” xuất hiện trong cột D.
Tìm phòng ban của nhân viên Kristen Tate bằng INDEX và XMATCH
Sự kết hợp này cũng xử lý các tìm kiếm phức tạp, chẳng hạn như khi bạn cần tìm mức lương của nhân viên trong phòng Sales có ID nhân viên cao nhất:
=INDEX(A:A, XMATCH(MAX(IF(R:R="Sales", H:H)), IF(R:R="Sales", H:H), 0))Công thức mảng này tìm ID nhân viên tối đa trong phòng Sales, sau đó trả về mức lương của người đó. Nếu bạn thử làm điều này với VLOOKUP, bạn sẽ cần nhiều cột phụ và các cách giải quyết khác.
XMATCH kết hợp INDEX tìm lương nhân viên có ID cao nhất trong phòng Sales
Kết luận
Hàm XMATCH đã thực sự thay đổi cách tiếp cận các tác vụ tìm kiếm dữ liệu trên Excel. Với khả năng tìm kiếm đa hướng, kiểm soát khớp chính xác và tích hợp dễ dàng với hàm INDEX, XMATCH không chỉ là một sự thay thế mà còn là một nâng cấp vượt bậc so với VLOOKUP. Sự linh hoạt và mạnh mẽ của nó giúp tiết kiệm thời gian, giảm thiểu lỗi và giữ cho bảng tính của bạn luôn gọn gàng, hiệu quả.
Một khi bạn đã trải nghiệm mức độ kiểm soát và sự tiện lợi mà XMATCH mang lại, việc quay trở lại sử dụng VLOOKUP dường như không còn là một lựa chọn thực tế. Hãy thử áp dụng XMATCH vào quy trình làm việc của bạn và cảm nhận sự khác biệt. Nếu bạn có bất kỳ thắc mắc hay ví dụ thực tế nào khác muốn chia sẻ về hàm XMATCH, đừng ngần ngại để lại bình luận bên dưới nhé!
