Tìm hiểu về XMATCH: Hàm Excel thông minh hơn VLOOKUP rất nhiều

VLOOKUP từng khiến mọi người thất vọng vì những hạn chế cứng nhắc và cú pháp rườm rà. Nhưng giờ thì không còn nữa. Đã có một hàm Excel có thể xử lý tra cứu theo mọi hướng và cung cấp khả năng kiểm soát khớp chính xác.

XMATCH hoạt động theo mọi hướng bạn muốn

Điểm khó chịu nhất với VLOOKUP là yêu cầu bắt buộc phải tìm kiếm từ trái sang phải. Nếu cột tra cứu của bạn không nằm ngoài cùng bên trái trong phạm vi, bạn sẽ bị mắc kẹt. Bạn phải sắp xếp lại dữ liệu hoặc tìm giải pháp thay thế. Việc sắp xếp lại bảng tính chỉ để VLOOKUP hoạt động hiệu quả có thể tốn thời gian.

Hàm XMATCH của Excel loại bỏ vấn đề đau đầu này. Không giống như cấu trúc cứng nhắc của VLOOKUP, XMATCH tìm kiếm bất kỳ mảng nào theo bất kỳ hướng nào. Bạn có thể tìm dữ liệu ở bên trái cột tra cứu. Bạn cũng có thể tìm kiếm theo chiều dọc xuống một cột hoặc theo chiều ngang trên một hàng; XMATCH xử lý cả hai cách một cách dễ dàng.

Đây là cú pháp cơ bản:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Hãy cùng phân tích các tham số này:

• lookup_value: Giá trị bạn đang tìm kiếm.
• lookup_array: Phạm vi tìm kiếm.
• match_mode: Mức độ chính xác cần thiết của kết quả tìm kiếm (0 là chính xác, -1 là chính xác hoặc nhỏ nhất tiếp theo, 1 là chính xác hoặc lớn nhất tiếp theo).
• search_mode: Hướng tìm kiếm (1 là từ đầu đến cuối, -1 là từ cuối đến đầu, 2 là tìm kiếm nhị phân).

Đây là 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 "Kristen Tate" làm việc ở phòng ban nào, nhưng tên nhân viên lại nằm ở cột D trong khi phòng ban lại nằm ở cột B. Với VLOOKUP trong bảng tính Excel, thiết lập này sẽ buộc bạn phải sắp xếp lại dữ liệu vì bạn không thể nhìn sang trái.

Nhưng bạn có thể sử dụng hàm XMATCH để trả về vị trí của cô ấy trong cột tên như được hiển thị trong công thức sau:

=XMATCH("Kristen Tate", D:D, 0)

Nó đã thay đổi quy trình làm việc vì bạn không còn cần phải đếm các cột nữa. Với VLOOKUP trong bảng tính Excel, bạn phải liên tục đếm để xác định số chỉ mục cột. Nếu bạn thêm một cột mới vào dữ liệu, công thức của bạn đột nhiên bị lỗi do số chỉ mục thay đổi.

XMATCH cho phép bạn kiểm soát việc so khớp tốt hơn

Các tùy chọn so khớp của VLOOKUP bị giới hạn ở mức khớp chính xác hoặc khớp gần đúng - chỉ vậy thôi. Nếu dữ liệu không hoàn toàn sạch sẽ, bạn sẽ phải mất thời gian dọn dẹp một bảng tính Excel lộn xộn trước khi có thể bắt đầu tra cứu.

XMATCH giúp việc này trở nên dễ dàng với tham số match_mode. Đặt tham số này thành 0 cho các kết quả khớp chính xác, giống như tham số FALSE của VLOOKUP. Nhưng đây mới là lúc mọi thứ trở nên thú vị. Bạn có thể sử dụng -1 để tìm kết quả khớp chính xác hoặc giá trị nhỏ nhất tiếp theo, và 1 để tìm kết quả khớp chính xác hoặc giá trị lớn nhất tiếp theo.

Hãy xem xét 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:

=XMATCH(75000, H:H, -1)

Công thức này 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 - điều mà VLOOKUP gặp khó khăn trừ khi dữ liệu được sắp xếp hoàn hảo.

Tham số search_mode bổ sung thêm một lớp kiểm soát. 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 quan trọng khi bạn có các giá trị trùng lặp và cần mục nhập 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 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 ta.

=XMATCH("John Smith", D:D, 0, -1)

Hiểu rõ các tham số này sẽ giúp bạn 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 giảm số cột phụ trợ và thao tác dữ liệu. Các công thức của bạn trở nên mạnh mẽ hơn và bảng tính gọn gàng hơn.

XMATCH kết hợp hoàn hảo với INDEX

XMATCH tỏ ra tiện dụng hơn khi bạn kết hợp nó với hàm INDEX. Trong khi XMATCH tìm vị trí, INDEX sẽ lấy giá trị thực tế từ vị trí đó. Đây là một trong những hàm Excel hữu ích để tìm kiếm dữ liệu nhanh chóng, nhưng khi kết hợp với nhau, chúng tạo ra một tổ hợp tra cứu linh hoạt hơn.

Đây là cú pháp cơ bản khi bạn kết hợp cả hai:

=INDEX(return_array, XMATCH(lookup_value, lookup_array, [match_mode]))

Sự kết hợp này loại bỏ cơn ác mộng đếm cột của VLOOKUP. Thay vì phải nhớ rằng lương là cột thứ 8, 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 các 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 viên bằng INDEX và XMATCH:

=INDEX(R:R, XMATCH("Kristen Tate", D:D, 0))

Công thức trên đọc tự nhiên và trả về một giá trị từ cột R tại vị trí "Kristen Tate" xuất hiện trong cột D.

Tổ hợp này cũng xử lý các tra cứu phức tạp, chẳng hạn như khi bạn cần mức lương của nhân viên trong phòng Kinh doanh 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 lớn nhất trong phòng Kinh doanh, sau đó trả về mức lương của người đó. Nếu thử làm điều đó với VLOOKUP, bạn sẽ cần nhiều cột hỗ trợ và các giải pháp thay thế khác.

XMATCH đã hoàn toàn thay thế VLOOKUP trong quy trình làm việc. Tính linh hoạt theo hướng, khả năng kiểm soát khớp lệnh chính xác và tích hợp INDEX dễ dàng khiến nó trở thành hàm tra cứu mà mình cần nhất. Một khi đã trải nghiệm mức độ kiểm soát này, việc quay lại dùng VLOOKUP nghe có vẻ không thực tế.