Hàm XLOOKUP trong Excel sử dụng như thế nào?

Trong thời cập nhật mới, Microsoft đã đưa vào Excel những hàm mới với mục đích cải thiện và mở rộng tính năng. Trong số đó, Hàm XLOOKUP là một tên nổi bật được giới thiệu trong bản cập nhật mới nhất này.

Hàm XLOOKUP sẽ là người kế thừa thay thế cho hàm VLOOKUP quen thuộc trong Excel.

1. XLOOKUP là gì?

Hàm XLOOKUP là một đổi mới đầy hứa hẹn, là bí quyết mà Microsoft sẽ chính thức ra mắt để thay thế hàm VLOOKUP, nhằm khắc phục những hạn chế đang là vấn đề của VLOOKUP. Ngoài ra, XLOOKUP còn thay thế cho hàm HLOOKUP, cho phép nhìn ngược hướng, dò tìm chính xác và đặc biệt có thể chỉ định phạm vi là ô thay vì số cột. So với VLOOKUP, XLOOKUP không chỉ dễ sử dụng mà còn linh hoạt hơn nhiều.

Hiện tại, chỉ những người sử dụng chương trình Insiders mới có thể trải nghiệm hàm XLOOKUP. Với những người này, hãy nhanh tay cập nhật và khám phá ngay hàm XLOOKUP. Trong thời gian sắp tới, Microsoft sẽ hoàn thiện và phát hành hàm này cho tất cả người dùng Office 365.

2. Hướng dẫn sử dụng hàm XLOOKUP trong Excel

1. Cấu trúc hàm XLOOKUP

Cú pháp của hàm XLOOKUP được xây dựng như sau:

Hàm XLOOKUP sử dụng 5 đối số, trong đó chỉ có 3 đối số đầu tiên là bắt buộc, còn lại 2 đối số sau là tùy chọn, chi tiết như sau:

- lookup_value: Giá trị cần tìm kiếm
- lookup_array: Phạm vi (mảng) tìm kiếm
- return_array: Mảng chứa kết quả cần trả về
- [match_mode]: Chế độ khớp giá trị khi tìm kiếm
- [search_mode]

Ngoài ra, hãy lưu ý những thông tin quan trọng sau đây:

- lookup_value: Có thể là giá trị hoặc mảng giá trị bạn muốn tìm kiếm
- lookup_array và return_array: Cần khớp về kích thước

Với hàm XLOOKUP mới, các đối số cơ bản tương tự như VLOOKUP và HLOOKUP. Nếu bạn hiểu rõ cú pháp và cách sử dụng của 2 hàm trước, XLOOKUP sẽ trở nên dễ dàng và thuận tiện hơn.

2. Hướng dẫn sử dụng hàm XLOOKUP

XLOOKUP thực sự là siêu anh hùng của các hàm tìm kiếm!

Nếu bạn đã quen với VLOOKUP và HLOOKUP, thì XLOOKUP sẽ là bước đột phá của bạn. Hãy quên những giới hạn của chúng và đắm chìm vào thế giới mở rộ của XLOOKUP. Không chỉ có thể tìm kiếm bên trái, mà còn có thể làm nhiều điều khác biệt vô cùng độc đáo với sự kết hợp linh hoạt của INDEX và MATCH.

XLOOKUP giúp bạn mở rộng tầm nhìn của mình trong việc tìm kiếm. Hãy xem ví dụ sau, nơi bạn có thể dựa vào giá để tìm kiếm tên sản phẩm một cách dễ dàng:

Với XLOOKUP, bạn có thể tìm kiếm từ cuối lên đầu mà không gặp bất kỳ vấn đề gì!

XLOOKUP giúp bạn linh hoạt hóa quá trình tìm kiếm dữ liệu, chẳng hạn như việc tìm kiếm từ dưới lên. Điều này giải quyết những hạn chế của VLOOKUP và HLOOKUP một cách thông minh.

Với đối số thứ 5 của XLOOKUP là -1 (Tìm kiếm từ cuối lên đầu).

Chẳng hạn, với Hàng hoá 1, công thức ở ô E3 sử dụng tham số thứ 5 là 1 (Tìm kiếm từ đầu đến cuối), tương tự như VLOOKUP.

Còn công thức ở ô E5 với tham số thứ 5 là -1 (Tìm kiếm từ cuối lên đầu), giúp XLOOKUP dò tìm ngược từ cuối lên đầu của lookup_array (một tính năng mà VLOOKUP không thể thực hiện một cách thông thường).

XLOOKUP là siêu phẩm trả về một dãy giá trị

XLOOKUP là người bạn đồng hành hoàn hảo, giúp bạn trả về một loạt giá trị đáng kinh ngạc. Ví dụ:

Công thức tại ô I4 có dạng:

=XLOOKUP(H2, A4:A7, A4:F7)

Như vậy, hàm sẽ tìm kiếm giá trị trong ô H2 trong khoảng từ A4:A7 và trả về kết quả từ vùng A4:F7. Bạn cũng có thể áp dụng tương tự với các công thức ở ô I6 và ô I8.

XLOOKUP kết hợp các giá trị tìm kiếm một cách thông minh

XLOOKUP không chỉ tìm kiếm theo giá trị, mà còn cho phép bạn nối chuỗi trong giá trị tìm kiếm, vùng tìm kiếm và kết quả. Nếu bạn muốn tìm kiếm với nhiều điều kiện, hãy nối chúng lại và tận hưởng sự linh hoạt. Thử xem ví dụ bên dưới:

XLOOKUP có khả năng tìm kiếm trong một loạt giá trị

XLOOKUP không chỉ giúp bạn tìm kiếm giá trị mà còn hỗ trợ tìm kiếm trong một mảng giá trị. Hãy xem minh họa dưới đây để hiểu rõ hơn:

Trong ví dụ trên, hàm UNIQUE được sử dụng để loại bỏ giá trị trùng lặp và trả về danh sách duy nhất.

Khi tìm kiếm mảng giá trị, kết quả sẽ là một mảng giá trị theo chiều dọc (nhiều hàng, 1 cột). Điều này dẫn đến một số đặc điểm quan trọng:

- XLOOKUP có khả năng trả về mảng giá trị theo chiều ngang (1 hàng, nhiều cột) hoặc theo chiều dọc (nhiều hàng, 1 cột).
- Mảng trả về có thể là 1 chiều hoặc 2 chiều, nhưng trong trường hợp 2 chiều, ít nhất 1 chiều phải có kích thước là 1. Hàm không hỗ trợ trả về mảng giá trị lớn hơn 1 hàng và 1 cột (>=2 hàng và >=2 cột), chi tiết có thể xem ví dụ ở ô I12.

Kết hợp linh hoạt với XLOOKUP và các hàm khác

XLOOKUP mở ra khả năng kết hợp linh hoạt các hàm, cho phép bạn nhanh chóng lồng ghép chúng với nhau.

Ví dụ, sử dụng XLOOKUP để trả về mảng giá trị như trong hình dưới, bạn có thể tính tổng số hàng hóa phát sinh trong lần cuối cùng một cách dễ dàng.

Nối chuỗi linh hoạt trong kết quả của XLOOKUP

XLOOKUP là người đồng hành đắc lực, giúp bạn linh hoạt nối chuỗi trong kết quả trả về. Kết quả không chỉ là số, mà còn là sự kết hợp tuyệt vời giữa con số và văn bản. Hãy xem ví dụ dưới đây để thấy sự linh hoạt khi kết hợp mảng giá trị với chuỗi văn bản, từ số lượng đến thành tiền phát sinh của hàng hóa.

XLOOKUP lồng XLOOKUP để giải quyết vấn đề

Ví dụ minh họa ở dưới sẽ hướng dẫn bạn cách sử dụng XLOOKUP lồng XLOOKUP. Đơn giản, bạn có thể tính tổng số lượng của hàng hóa từ khi hàng hóa C xuất hiện đầu tiên cho đến khi nó xuất hiện lần cuối cùng.

XLOOKUP sửa lỗi chèn dòng, cột trong VLOOKUP và HLOOKUP

Khi muốn chèn thêm 1 cột (với VLOOKUP) hoặc 1 dòng (với HLOOKUP), sử dụng XLOOKUP để tránh sai lệch kết quả. Ví dụ:

Ví dụ:

- Chèn cột giữa A và B, kết quả D3 với XLOOKUP không bị ảnh hưởng. Ngược lại, VLOOKUP ở D2 đã đưa ra kết quả sai khiến cho D3 cũng bị ảnh hưởng.

- Tương tự, khi chèn dòng giữa dòng 7 và 8, kết quả H6 với XLOOKUP vẫn đúng. Trong khi đó, HLOOKUP ở H5 đã dẫn đến kết quả sai do ảnh hưởng từ việc chèn dòng.

XLOOKUP trong Excel là một đứa con mới, nó mang theo giải pháp cho những vấn đề mà cả VLOOKUP và HLOOKUP đã mắc phải. Với tính năng đa dạng và sự linh hoạt, XLOOKUP đem đến những trải nghiệm tuyệt vời. Hãy thử và tận hưởng ngay nếu bạn là người sử dụng bản Insider cho Office 365 nhé!