- – Mặc định tìm kiếm của VLOOKUP là xấp xỉ
- – Không hỗ trợ thao tác thêm/xóa cột
- – Không thể tìm kiếm sang trái
- – Không thể tìm kiếm giá trị theo trình tự ngược lại
- – Không thể tìm kiếm giá trị lớn hơn kế tiếp
- – Tham chiếu tới nhiều ô hơn cần thiết
Cách sử dụng chức năng XLOOKUP
– 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
– 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)
Ví dụ cụ thể
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
