Hàm VLOOKUP luôn là một trong những hàm mạnh nhất của Excel. Nó cho phép bạn tìm kiếm các giá trị trong cột đầu tiên của bảng và trả về giá trị từ các trường ở bên phải. Nhưng Excel cũng có một hàm gọi là XLOOKUP, cho phép bạn tìm kiếm một giá trị trong bất kỳ cột hoặc hàng nào và trả về dữ liệu từ bất kỳ cột nào khác.
Cách XLOOKUP Hoạt động
Hàm XLOOKUP dễ sử dụng hơn nhiều so với hàm Vlookup, vì thay vì chỉ định giá trị cho cột kết quả, bạn có thể chỉ định toàn bộ phạm vi.
Chức năng này cũng cho phép bạn tìm kiếm cả cột và hàng, định vị giá trị tại ô giao nhau.
Các tham số của hàm XLOOKUP như sau:
=XLOOKUP (giá trị tìm kiếm, giá trị tìm kiếm, giá_trị_mạch, trả về_mảng, [chế độ khớp], [chế độ tìm kiếm])
- lookup_value: Giá trị bạn muốn tìm kiếm
- lookup_array: Mảng (cột) bạn muốn tìm kiếm
- return_array: Kết quả (cột) bạn muốn lấy một giá trị từ
- match_mode (tùy chọn): Chọn đối sánh chính xác (0), đối sánh chính xác hoặc giá trị nhỏ nhất tiếp theo (-1) hoặc đối sánh ký tự đại diện (2).
- search_mode (tùy chọn): Chọn tìm kiếm bắt đầu từ mục đầu tiên trong cột (1), mục cuối cùng trong cột (-1), tìm kiếm nhị phân tăng dần (2) hoặc tìm kiếm nhị phân giảm dần (-2).
Sau đây là một số cách tra cứu phổ biến nhất mà bạn có thể thực hiện với hàm XLOOKUP.
Cách tìm kiếm một kết quả duy nhất bằng XLOOKUP
Cách dễ nhất để sử dụng XLOOKUP là tìm kiếm một kết quả duy nhất bằng cách sử dụng điểm dữ liệu từ một cột.
-
Bảng tính ví dụ này là danh sách các đơn đặt hàng do đại diện bán hàng gửi, bao gồm mặt hàng, số lượng đơn vị, chi phí và tổng doanh số.
Image -
Nếu bạn muốn tìm chương trình giảm giá đầu tiên trong danh sách do một đại diện bán hàng cụ thể gửi, bạn có thể tạo hàm XLOOKUP để tìm kiếm tên trong cột Đại diện. Hàm sẽ trả về kết quả từ cột Total. Hàm XLOOKUP cho điều này là:
=XLOOKUP (I2, C2: C44, G2: G44, 0, 1)
- I2: Trỏ đến Tên đại diệnô tìm kiếm
- C2: C44: Đây là cột Đại diện, là mảng tra cứu
- G2: G33: Đây là cột Tổng, là mảng trả về
- 0: Chọn kết hợp chính xác
- 1: Chọn trận đấu đầu tiên trong các kết quả
-
Khi bạn nhấn Nhậpvà nhập tên của đại diện bán hàng, ô Tổng kết quả sẽ hiển thị cho bạn kết quả đầu tiên trong bảng cho đại diện bán hàng đó.
Image -
Nếu bạn muốn tìm kiếm đợt giảm giá gần đây nhất (vì bảng được sắp xếp theo ngày theo thứ tự ngược lại), hãy thay đổi đối số XLOOKUP cuối cùng thành- 1, đối số này sẽ bắt đầu tìm kiếm từ ô cuối cùng trong mảng tra cứu và thay vào đó cung cấp cho bạn kết quả đó.
Image -
Ví dụ này cho thấy một tìm kiếm tương tự mà bạn có thể thực hiện với hàm VLOOKUP bằng cách sử dụng cột Đại diện làm cột đầu tiên của bảng tra cứu. Tuy nhiên, XLOOKUP cho phép bạn tìm kiếm bất kỳ cột nào theo cả hai hướng. Ví dụ: nếu bạn muốn tìm đại diện bán hàng đã bán đơn hàng Binder đầu tiên trong năm, bạn sẽ sử dụng hàm folloing XLOOKUP:
=XLOOKUP (I2, D2: D44, C2: C44, 0, 1)
- D2: Trỏ đến ô tìm kiếm Mục
- D2: D44: Đây là cột Mục, là mảng tra cứu
- C2: C44: Đây là cột Đại diện, là mảng trả về ở bên trái của mảng tra cứu
- 0: Chọn kết hợp chính xác
- 1: Chọn trận đấu đầu tiên trong các kết quả
-
Lần này, kết quả sẽ là tên của người đại diện bán hàng đã bán được đơn hàng đóng bìa đầu tiên trong năm.
Image
Thực hiện Kết hợp theo chiều dọc và chiều ngang với XLOOKUP
Một khả năng khác của XLOOKUP mà hàm VLOOKUP không có là khả năng thực hiện cả tìm kiếm dọc và ngang, nghĩa là bạn có thể tìm kiếm một mục trong một cột và trên một hàng.
Tính năng tìm kiếm kép này là sự thay thế hiệu quả cho các hàm Excel khác như INDEX, MATCH hoặc HLOOKUP.
-
Trong bảng tính ví dụ sau, doanh thu của mỗi đại diện bán hàng được chia theo quý. Nếu bạn muốn xem doanh số bán hàng trong quý thứ ba cho một đại diện bán hàng cụ thể, mà không có chức năng XLOOKUP, thì loại tìm kiếm này sẽ rất khó khăn.
Image -
Với chức năng XLOOKUP, loại tìm kiếm này thật dễ dàng. Bằng cách sử dụng chức năng XLOOKUP kết hợp giữa folloing, bạn có thể tìm kiếm doanh số bán hàng trong quý thứ ba cho một đại diện bán hàng cụ thể:
=XLOOKUP (J2, B2: B42, XLOOKUP (K2, C1: H1, C2: H42))
- J2: Trỏ đến ô tìm kiếm Đại diện
- B2: B42: Đây là cột Mục, là mảng tra cứu cột
- K2: Trỏ đến ô tìm kiếm Quý
- C1: H1: Đây là mảng tra cứu hàng
- C2: H42: Đây là mảng tra cứu số tiền trong mỗi quý
Hàm XLOOKUP lồng nhau này trước tiên xác định đại diện bán hàng và hàm XLOOKUP mới xác định quý mong muốn. Giá trị trả về sẽ là ô mà hai ô đó giao nhau.
-
Kết quả cho công thức này là thu nhập quý một của người đại diện có tên Thompson.
Image
Sử dụng Hàm XLOOKUP
Chức năng XLOOKUP chỉ khả dụng cho người đăng ký Người dùng nội bộ Office, nhưng sẽ sớm được triển khai cho tất cả người đăng ký Microsoft 365.
Nếu bạn muốn tự mình kiểm tra chức năng, bạn có thể trở thành Người dùng nội bộ Office. Chọn File> Account, sau đó chọn menu thả xuống Office Insiderđể đăng ký.
Sau khi bạn tham gia chương trình Người dùng nội bộ Office, phiên bản Excel đã cài đặt của bạn sẽ nhận được tất cả các bản cập nhật mới nhất và bạn có thể bắt đầu sử dụng hàm XLOOKUP.
