Hướng dẫn hàm XLOOKUP và ví dụ cụ thể

VLOOKUP là một hàm để tìm kiếm rất hữu ích trong Excel. Tuy nhiên, nó vẫn còn một số hạn chế như sau:

• Mặc định tìm kiếm của VLOOKUP là tương đối, không chính xác
• Không hỗ trợ thao tác thêm/xóa cột
• Không thể tìm kiếm từ phải sang trái

Vì vậy, XLOOKUP đã xuất hiện nhằm giải quyết các hạn chế mà hàm VLOOKUP đang gặp phải và cung cấp thêm một số chức năng. UniTrain trong bài viết này sẽ thông qua một số ví dụ cụ thể để hướng dẫn cách sử dụng hàm XLOOKUP.

1. Cách sử dụng chức năng XLOOKUP

Cú pháp: =XLOOKUP(lookup_value, lookup_array, return_array,match_mode,search_mode)

Trong đó

• lookup_value: Là giá trị tìm kiếm
• lookup_array: Là vùng (mảng) tìm kiếm
• return_array: Là vùng (mảng) mà ở đó người dùng muốn trả về kết quả
• match_mode: Là lựa chọn chế độ khớp giá trị khi tìm kiếm
• search_mode: Là lựa chọn chế độ tìm kiếm

Lưu ý

– lookup_value: Có thể sẽ là 1 giá trị hoặc là 1 mảng giá trị bạn muốn tìm kiếm
– lookup_array và return_array: Cần được khớp nhau về kích thước
– match_mode: Mặc định là tìm kiếm chính xác (0 – Exact match)
– search_mode: Mặc định là tìm kiếm từ đầu đến cuối (1 – Search first-to-last)

XLOOKUP dùng đơn giản thay thế cho VLOOKUP và HLOOKUP thì bạn chỉ cần viết cấu trúc hàm đến phần thứ 3. Cụ thể: =XLOOKUP(lookup_value, lookup_array, return_array)

2. Ví dụ cụ thể

Ví dụ 1

Trong ví dụ này, chúng ta đang tìm kiếm thông tin nhân viên dựa trên số ID nhân viên. Không giống như VLOOKUP, XLOOKUP có thể trả về một mảng với nhiều mục, cho phép một công thức đơn để trả về cả Tên nhân viên (Employee Name) và Bộ phận (Department) từ các ô C5:D14.

Ví dụ 2

Ví dụ này sẽ thêm đối số if_not_found vào ví dụ 2.

Ví dụ 3

Trong ví dụ sau đây, chúng ta sẽ đi tìm Thu nhập cá nhân (Income) và Tỷ suất thuế (Tax Rate). Công thức trả về 0 nếu không tìm thấy gì. Đối số match_mode được đặt là 1, điều này có nghĩa là hàm sẽ tìm kiếm kết quả phù hợp chính xác và nếu nó không thể tìm thấy giá trị nào, nó sẽ trả về mục lớn hơn tiếp theo. Cuối cùng, đối số search_mode được đặt là 1, điều này có nghĩa là hàm sẽ tìm kiếm từ mục đầu tiên đến cuối cùng.

Lưu ý: Trong XLOOKUP, cột lookup_array nằm ở bên phải của cột return_array, ưu việt hơn hàm VLOOKUP chỉ có thể tìm kiếm từ trái sang phải.

Ví dụ 4

Tiếp theo, chúng ta sẽ sử dụng hàm XLOOKUP lồng nhau để thực hiện cả khớp dọc và ngang. Trong trường hợp này, trước tiên, hàm sẽ tìm kiếm Lợi nhuận gộp (Gross Profit) trong cột B, sau đó tìm Qtr1 ở hàng trên cùng của bảng (phạm vi C5: F5) và trả về giá trị tại giao điểm của cả hai. Điều này tương tự như việc sử dụng các hàm INDEX và MATCH kết hợp. Bạn cũng có thể sử dụng XLOOKUP để thay thế hàm HLOOKUP.

Công thức trong các ô D3: F3 là: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)).

Ví dụ 5

Ví dụ này sử dụng hàm SUM và hai hàm XLOOKUP được lồng với nhau để tính tổng tất cả các giá trị giữa hai phạm vi. Trong trường hợp này, chúng ta muốn tính tổng các giá trị từ Banana đến Grape, bao gồm cả Pear.

Công thức trong ô E3 là: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

Tính năng này hoạt động như thế nào? XLOOKUP trả về một phạm vi, vì vậy khi tính năng này tính toán, công thức sẽ kết thúc trông như thế này: =SUM($E$7:$E$9). Bạn có thể tự mình xem cách thức hoạt động của tính năng này bằng cách chọn một ô có công thức XLOOKUP tương tự như ô này, sau đó đi đến Formulas > Formula Auditing > Evaluate Formula và nhấn nút Evaluate để xem.

Xem thêm

Hướng dẫn Hàm Vlookup dò tìm dữ liệu thay đổi

Cách dùng hàm If trong Excel và ứng dụng thực tế