Cách sử dụng XLOOKUP và ví dụ

Ví dụ 1

Ví dụ này  sử dụng XLOOKUP đơn giản để tìm kiếm một Tên quốc gia (Country Name), sau đó trả về tiền tố điện thoại của quốc gia đó. Trong ví dụ này, công thức chỉ đơn giản gồm các đối số lookup_value (ô F2), lookup_array (phạm vi B2: B11) và return_array (phạm vi D2:D11) .

Lưu ý: XLOOKUP khác với hàm VLOOKUP. Trong khi Xlookup dùng các mảng tra cứu và mảng trả về riêng biệt thì VLOOKUP sử dụng một bảng đơn, theo sau là một số chỉ số cột. Công thức VLOOKUP tương đương trong trường hợp này sẽ là: =VLOOKUP (F2,B2:D11,3,FALSE)

Ví dụ 2

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ụ 3

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

Ví dụ 4

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ụ 5

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ụ 6

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

Chính thức: Hàm Xlookup hiện đã có sẵn cho người dùng Oficce 365

Cách loại bỏ giá trị trùng lặp với Power Query