VLOOKUP từ lâu đã trở thành một công cụ quen thuộc trong Excel, nhưng không ít lần người dùng cảm thấy “ức chế” bởi những giới hạn cứng nhắc và cú pháp đôi khi rườm rà của nó. Đặc biệt là yêu cầu cột dò tìm phải nằm ở ngoài cùng bên trái. Tuy nhiên, mọi chuyện đã thay đổi kể từ khi một hàm Excel mới xuất hiện, có khả năng dò tìm theo bất kỳ hướng nào và cung cấp khả năng kiểm soát đối sánh chính xác hơn nhiều. Đó chính là hàm XMATCH.
Với XMATCH, việc dò tìm dữ liệu trong Excel không còn là một thách thức, ngay cả khi dữ liệu của bạn không được sắp xếp theo chuẩn “VLOOKUP”. Hàm này mang đến sự linh hoạt đáng kinh ngạc, giúp bạn tiết kiệm thời gian và công sức trong việc xử lý các bảng tính phức tạp, khẳng định vị thế là công cụ tối ưu cho các bài toán dò tìm dữ liệu hiện đại.
XMATCH – Khả Năng Dò Tìm Linh Hoạt Mọi Hướng, Phá Vỡ Giới Hạn Của VLOOKUP
“Nỗi Ám Ảnh” Giới Hạn Trái-Sang-Phải Của VLOOKUP
Một trong những “nỗi ám ảnh” lớn nhất khi làm việc với VLOOKUP chính là yêu cầu cứng nhắc về vị trí cột. Nếu cột chứa giá trị bạn muốn tìm (lookup column) không phải là cột ngoài cùng bên trái trong phạm vi dữ liệu đã chọn, bạn sẽ gặp bế tắc. Để khắc phục, bạn buộc phải sắp xếp lại dữ liệu, chèn thêm cột phụ hoặc tìm kiếm các giải pháp thay thế. Điều này không chỉ tốn thời gian mà còn làm gián đoạn quy trình làm việc và làm cho bảng tính trở nên kém gọn gàng. VLOOKUP không có khả năng “nhìn” sang trái, và đây là một điểm yếu cố hữu khiến nhiều người dùng phải đau đầu.
XMATCH: Hàm Dò Tìm Đa Chiều Mới
Hàm XMATCH của Excel đã loại bỏ hoàn toàn “cơn đau đầu” này. Không giống như 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 thấy dữ liệu nằm ở bên trái cột dò tìm của mình mà không cần phải thay đổi cấu trúc bảng. XMATCH cũng xử lý việc tìm kiếm theo chiều dọc (xuống một cột) hoặc chiều ngang (ngang qua một hàng) một cách dễ dàng, mang lại sự linh hoạt tối đa cho người dùng.
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 chi tiết các tham số này:
- lookup_value: Giá trị bạn muốn tìm kiếm.
- lookup_array: Phạm vi (mảng) mà bạn sẽ tìm kiếm giá trị.
- match_mode: Cách thức đối sánh (0 cho đối sánh chính xác, -1 cho đối sánh chính xác hoặc giá trị nhỏ nhất tiếp theo, 1 cho đối sánh 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).
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 mà “Kristen Tate” đang làm việc, nhưng tên nhân viên nằm ở cột D trong khi tên phòng ban lại nằm ở cột B. Với VLOOKUP, bạn sẽ phải sắp xếp lại dữ liệu. Tuy nhiên, với hàm XMATCH, bạn có thể dễ dàng tìm 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 một nhân viên cụ thể
Hàm XMATCH đã thay đổi đáng kể quy trình làm việc của tôi vì tôi không còn phải bận tâm đến việc đếm số cột. Với VLOOKUP trong Excel, bạn liên tục phải đếm để xác định chỉ số cột (column index number). 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 có thể bị lỗi vì chỉ số cột đã thay đổi.
Kiểm Soát Kết Quả Dò Tìm Tối Ưu Với Tham Số match_mode Và search_mode Của XMATCH
match_mode: Từ Chính Xác Đến Gần Đúng Tối Ưu
Các tùy chọn đối sánh của VLOOKUP khá hạn chế, chỉ bao gồm đối sánh chính xác (FALSE) hoặc đối sánh gần đúng (TRUE). Nếu dữ liệu của bạn không “sạch” hoàn toàn, 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 dò tìm.
XMATCH làm cho việc này trở nên dễ dàng hơn với tham số match_mode. Bạn có thể đặt nó bằng 0 để tìm kiếm đối sánh chính xác, giống 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 đối sánh chính xác hoặc giá trị nhỏ nhất tiếp theo (nếu không tìm thấy giá trị chính xác), và 1 cho đối sánh 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 bộ 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 trong 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 gặp khó khăn trừ khi dữ liệu của bạn được sắp xếp hoàn hảo.
Hàm XMATCH trong Excel trả về mức lương nhân viên gần 75000 USD
search_mode: Kiểm Soát Hướng Và Tần Suất Dò Tìm
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), thì -1 tìm kiếm từ cuối đến đầu. Tham số này rất quan trọng khi bạn có các giá trị trùng lặp và cần tìm mục nhập 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 bộ 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 các tham số này giúp bạn tiếp cận việc dò tì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 phải sử dụng 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 được giữ gọn gàng hơn.
Sự Kết Hợp Hoàn Hảo: XMATCH Và INDEX Trong Excel
Thay Thế Hạn Chế Của VLOOKUP Bằng INDEX-XMATCH
Hàm XMATCH trở nên hữu ích hơn rất nhiều khi bạn kết hợp nó với hàm INDEX. Trong khi XMATCH tìm vị trí, INDEX sẽ truy xuất giá trị thực 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, chúng tạo ra một cặp dò tìm linh hoạt và mạnh mẽ hơn nhiều.
Cú pháp cơ bản khi bạn kết hợp cả hai như sau:
=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ứ 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 viên bằng cách sử dụng INDEX và XMATCH:
=INDEX(B:B, XMATCH("Kristen Tate", D:D, 0))Công thức trên đọc một cách tự nhiên và trả về giá trị từ cột B tại vị trí mà “Kristen Tate” xuất hiện trong cột D (giả sử cột B là cột Phòng ban).
Hàm XMATCH trong Excel trả về phòng ban của nhân viên cụ thể
Giải Quyết Các Bài Toán Dò Tìm Phức Tạp Với INDEX-XMATCH
Sự kết hợp này cũng xử lý được các tác vụ dò tì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))Lưu ý: Đây là một công thức mảng, có thể cần được nhập bằng Ctrl+Shift+Enter trong các phiên bản Excel cũ hơn, hoặc tự động hoạt động trong các phiên bản Excel 365 mới hơn.
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 cố gắng làm điều đó với VLOOKUP, bạn sẽ cần nhiều cột phụ và các cách giải quyết khác nhau.
Hàm XMATCH trong Excel trả về mức lương của nhân viên có ID cao nhất
XMATCH đã hoàn toàn thay thế VLOOKUP trong quy trình làm việc của tôi. Khả năng linh hoạt về hướng dò tìm, kiểm soát đối sánh chính xác và tích hợp dễ dàng với hàm INDEX đã biến XMATCH thành hàm dò tìm mà tôi cần nhất. Một khi bạn trải nghiệm mức độ kiểm soát này, việc quay lại VLOOKUP dường như không còn thực tế. Đừng quên theo dõi các bản cập nhật và hướng dẫn tiếp theo từ thuthuatdidong.net để làm chủ Excel và nhiều công nghệ khác!
