Nếu bạn đã từng “vật lộn” với những hạn chế cố hữu và cú pháp rườm rà của hàm VLOOKUP trong Excel, bạn không đơn độc. VLOOKUP có thể gây ra nhiều phiền toái khi bạn cần tra cứu dữ liệu mà không muốn sắp xếp lại bảng tính hay đối phó với lỗi công thức. Tin tốt là có một hàm Excel mạnh mẽ hơn đã xuất hiện, mang đến khả năng tra cứu linh hoạt theo mọi hướng và kiểm soát đối sánh chính xác, giúp bạn loại bỏ mọi “ám ảnh” về VLOOKUP. Đó chính là hàm XMATCH.
XMATCH không chỉ là một sự thay thế đơn thuần mà còn là một bước tiến đáng kể trong việc xử lý dữ liệu trên bảng tính. Hàm này giải quyết triệt để những vấn đề nan giải nhất của VLOOKUP, từ yêu cầu tìm kiếm theo một hướng cụ thể đến khả năng kiểm soát khớp chính xác. Việc thành thạo XMATCH sẽ thay đổi hoàn toàn quy trình làm việc của bạn, giúp công việc quản lý và phân tích dữ liệu trở nên hiệu quả và ít lỗi hơn.
XMATCH: Tìm Kiếm Linh Hoạt Mọi Hướng, Phá Vỡ Giới Hạn Của VLOOKUP
Hạn chế lớn nhất của hàm VLOOKUP chính là yêu cầu cứng nhắc về việc tra cứu từ trái sang phải. Nếu cột chứa giá trị bạn muốn tìm kiếm không nằm ở cột ngoài cùng bên trái của vùng dữ liệu, bạn sẽ gặp khó khăn. Điều này buộc người dùng phải sắp xếp lại dữ liệu, tạo thêm các cột phụ hoặc tìm kiếm các giải pháp phức tạp khác, gây lãng phí thời gian và làm lộn xộn cấu trúc bảng tính.
Hàm XMATCH của Excel đã loại bỏ hoàn toàn rào cản này. Khác với cấu trúc cứng nhắc của VLOOKUP, XMATCH có thể 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 tra cứu, tìm kiếm dọc theo một cột hoặc tìm kiếm ngang qua một hàng; XMATCH đều xử lý một cách dễ dàng và hiệu quả.
Dưới đây là cú pháp cơ bản của hàm XMATCH:
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Hãy cùng phân tích chi tiết các tham số này:
- lookup_value: Giá trị mà bạn muốn tìm kiếm.
- lookup_array: Vùng dữ liệu (một cột hoặc một hàng) mà bạn muốn tìm kiếm giá trị đó.
- match_mode: Cách thức đối sánh (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ừ đầu đến cuối, -1 cho từ cuối đến đầu, 2 cho tìm kiếm nhị phân tăng dần, -2 cho tìm kiếm nhị phân giảm dần).
Để hiểu rõ hơn, hãy xét một ví dụ thực tế từ cơ sở dữ liệu nhân sự. Giả sử bạn cần tìm xem “Kristen Tate” thuộc phòng ban nào, nhưng tên nhân viên nằm ở cột D trong khi thông tin phòng ban lại nằm ở cột B. Với VLOOKUP, cấu trúc này sẽ buộc bạn phải sắp xếp lại dữ liệu vì VLOOKUP không thể tra cứu 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ư công thức sau:
=XMATCH("Kristen Tate", D:D, 0)
Hàm XMATCH trong Excel trả về vị trí của nhân viên "Kristen Tate" trong cột tên
Hàm XMATCH đã thay đổi hoàn toàn quy trình làm việc của nhiều người vì không còn cần phải đếm số cột. Với VLOOKUP, bạn thường xuyên phải đếm để xác định chỉ số cột cần lấy dữ liệu. Nếu bạn thêm một cột mới vào dữ liệu, công thức VLOOKUP của bạn có thể bị lỗi vì chỉ số cột đã thay đổi. XMATCH khắc phục triệt để điều này.
XMATCH: Kiểm Soát Đối Sánh Chính Xác Hơn Với match_mode và search_mode
Các tùy chọn đối sánh của VLOOKUP rất hạn chế, chỉ bao gồm khớp chính xác hoặc khớp tương đối đơn giản. Nếu dữ liệu của bạn không hoàn toàn “sạch”, bạn sẽ phải mất thời gian dọn dẹp bảng tính Excel lộn xộn trước khi có thể bắt đầu tra cứu.
XMATCH làm cho việc này trở nên dễ dàng với tham số match_mode của nó. Bạn có thể đặt nó bằng 0 để tìm kiếm khớp chính xác, tương tự như tham số FALSE của VLOOKUP. Nhưng điểm đặc biệt là 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 để tìm kiếm 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 sự. Để 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 có thể sử dụng công thức sau:
=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 trong cột H mà không vượt quá giá trị mục tiêu của bạn – điều mà VLOOKUP rất khó khăn để thực hiện trừ khi dữ liệu của bạn đã được sắp xếp một cách hoàn hảo.
Ví dụ hàm XMATCH trong Excel với tham số match_mode -1 để tìm mức lương gần nhất không vượt quá 75000 USD
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 rất hữu ích khi bạn có các giá trị trùng lặp và cần tìm kiếm lần xuất hiện gần đây nhất.
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)Việc hiểu rõ các tham số này giúp bạn tiếp cận việc tra cứu 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ần các 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 luôn được giữ gọn gàng.
Kết Hợp XMATCH Với INDEX: Bộ Đôi Tra Cứu Dữ Liệu Mạnh Mẽ Nhất Excel
XMATCH phát huy tối đa sức mạnh khi bạn kết hợp nó với hàm INDEX. Trong khi XMATCH tì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 Excel hữu ích để tìm dữ liệu nhanh chóng, nhưng khi kết hợp với nhau, chúng tạo thành một bộ đôi tra cứu linh hoạt và mạnh mẽ hơn nhiều.
Dưới đâ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 mức lương là cột thứ tám, bạn chỉ cần chỉ định trực tiếp cột 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.
Giả sử bạn cần tìm phòng ban của Kristen Tate từ dữ liệu nhân sự bằng INDEX và XMATCH:
=INDEX(R:R, XMATCH("Kristen Tate", D:D, 0))Công thức trên rất dễ đọc và sẽ trả về một giá trị từ cột R (cột phòng ban) tại vị trí mà “Kristen Tate” xuất hiện trong cột D (cột tên nhân viên).
Cách kết hợp hàm INDEX và XMATCH trong Excel để tìm phòng ban của nhân viên "Kristen Tate"
Sự kết hợp này cũng xử lý các yêu cầu tra cứu phức tạp hơn, chẳng hạn như khi bạn cần tìm mức lương của nhân viên thuộc 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 sẽ 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 cố gắng thực hiện điều này với VLOOKUP, bạn sẽ cần nhiều cột phụ và các giải pháp phức tạp khác.
Công thức mảng phức tạp với hàm INDEX và XMATCH trong Excel để tìm mức lương dựa trên điều kiện nhiều tiêu chí (ID nhân viên cao nhất trong phòng Sales)
XMATCH đã hoàn toàn thay thế VLOOKUP trong quy trình làm việc của nhiều người dùng Excel. Sự linh hoạt về hướng tìm kiếm, khả năng kiểm soát đối sánh chính xác và tích hợp dễ dàng với INDEX khiến nó trở thành hàm tra cứu dữ liệu cần thiết nhất. Một khi bạn đã trải nghiệm cấp độ kiểm soát này, việc quay lại VLOOKUP không còn là một lựa chọn thực tế. Hãy bắt đầu áp dụng XMATCH ngay hôm nay để nâng cao hiệu suất làm việc của bạn trong Excel!
