Từ lâu, hàm VLOOKUP trong Excel đã khiến nhiều người dùng cảm thấy “bất lực” bởi những hạn chế cứng nhắc và cú pháp phức tạp của nó. Đặc biệt là yêu cầu cột tra cứu phải nằm ở ngoài cùng bên trái. Tuy nhiên, mọi chuyện đã thay đổi! Một hàm Excel mới, linh hoạt hơn, có thể xử lý việc tra cứu theo bất kỳ hướng nào và cung cấp khả năng kiểm soát khớp chính xác, đã xuất hiện và giải quyết triệt để những vấn đề này. Đó chính là hàm XMATCH, công cụ tra cứu dữ liệu mạnh mẽ mà bạn không thể bỏ qua trên thuthuat360.net.
XMATCH Hoạt Động Linh Hoạt Theo Mọi Hướng Bạn Muốn
Sự khó chịu lớn nhất của 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 tra cứu không phải là cột ngoài cùng bên trái trong phạm vi dữ liệu, bạn sẽ phải đối mặt với việc sắp xếp lại dữ liệu hoặc tìm các giải pháp thay thế phức tạp. Điều này thực sự lãng phí thời gian khi bạn phải tái cấu trúc bảng tính chỉ để “làm hài lòng” VLOOKUP.
Hàm XMATCH của Excel đã xóa 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 cho phép bạn tìm kiếm trong bất kỳ mảng nào và theo bất kỳ hướng nào. 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 của mình. Hơn nữa, dù bạn cần tìm kiếm theo chiều dọc xuống một cột hay theo chiều 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 tìm hiểu 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 muốn tìm kiếm giá trị đó trong Excel.
- match_mode: Cách thức bạn muốn 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 kế tiếp, 1 cho khớp chính xác hoặc giá trị lớn nhất kế tiếp, 2 cho khớp ký tự đại diện).
- 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 theo thứ tự tăng dần, -2 cho tìm kiếm nhị phân theo thứ tự giảm dần).
Để hiểu rõ hơn, hãy xem một ví dụ thực tế từ một cơ sở dữ liệu nhân viên. Giả sử bạn cần tìm phòng ban mà “Kristen Tate” làm việc, nhưng tên nhân viên lại nằm ở cột D trong khi phòng ban ở 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ể tra cứu sang trái.
Với hàm XMATCH, bạn có thể dễ dàng trả về vị trí của cô ấy trong cột tên như công thức sau:
=XMATCH("Kristen Tate", D:D, 0)
XMATCH trong Excel trả về vị trí nhân viên Kristen Tate
Hàm XMATCH thực sự đã thay đổi quy trình làm việc của tôi, vì tôi không còn cần phải đếm cột nữa. Với VLOOKUP, 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ác công thức của bạn có thể bị lỗi do số chỉ mục thay đổi.
XMATCH Mang Lại Khả Năng Kiểm Soát Tra Cứu Tốt Hơn
Các tùy chọn khớp của VLOOKUP bị giới hạn chỉ ở khớp chính xác hoặc khớp tương đối — chỉ có vậy. Nếu dữ liệu của bạn không hoàn hảo, bạn sẽ mất thời gian để dọn dẹp bảng 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 của nó. Đặt tham số này là 0 để tìm kiếm khớp chính xác, tương tự như tham số FALSE của VLOOKUP. Nhưng điều thú vị là bạn có thể sử dụng -1 để tìm kiếm giá trị khớp chính xác hoặc giá trị nhỏ nhất kế tiếp, và 1 để tìm kiếm giá trị khớp chính xác hoặc giá trị lớn nhất kế tiếp.
Xem xét ví dụ về 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 – đ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.
XMATCH trong Excel tìm mức lương gần 75000 USD nhất
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 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.
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 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 nhiều. Mức độ kiểm soát này đồng nghĩa với việc ít phải dùng đến các cột trợ giúp 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.
XMATCH Kết Hợp Hoàn Hảo Với Hàm INDEX
XMATCH càng trở nên 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ẽ truy xuất giá trị thực tế từ vị trí đó. Đây là một trong những hàm Excel hữu ích giúp tìm dữ liệu nhanh chóng, nhưng khi kết hợp lại, chúng tạo ra một bộ đôi tra cứu linh hoạt vượt trộ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 nỗi ám ảnh đế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 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 rất tự nhiên và trả về một giá trị từ cột R tại vị trí mà “Kristen Tate” xuất hiện trong cột D.
Kết hợp INDEX và XMATCH trong Excel để tìm phòng ban của Kristen Tate
Sự kết hợp này cũng xử lý được 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 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 cố gắng làm điều đó với VLOOKUP, bạn sẽ cần nhiều cột trợ giúp và các giải pháp thay thế khác.
XMATCH trong Excel trả về mức lương của nhân viên có ID cao nhất
Hàm XMATCH đã thay thế hoàn toàn VLOOKUP trong quy trình làm việc của tôi. Sự linh hoạt về hướng tra cứu, khả năng kiểm soát khớp chính xác và việc tích hợp dễ dàng với INDEX khiến nó trở thành hàm tra cứu 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ế nữa. Hãy khám phá thêm nhiều thủ thuật và hàm Excel hữu ích khác trên thuthuat360.net để tối ưu hóa công việc của bạn!
