Cách sử dụng hàm XLOOKUP trong Excel -

Tóm lược

Hàm XLOOKUP trong Excel là một sự thay thế linh hoạt và hiện đại cho các hàm cũ hơn như VLOOKUP, HLOOKUP và LOOKUP. XLOOKUP hỗ trợ đối sánh gần đúng và chính xác, ký tự đại diện (*?) Cho đối sánh từng phần và tra cứu trong phạm vi dọc hoặc ngang.

Mục đích

Tra cứu giá trị trong phạm vi hoặc mảng

Giá trị trả lại

Đối sánh (các) giá trị từ mảng trả về

Cú pháp

= XLOOKUP (tra cứu, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Tranh luận

  • tra cứu - Giá trị tra cứu.
  • lookup_array - Mảng hoặc dải ô cần tìm kiếm.
  • return_array - Mảng hoặc dải ô sẽ trả về.
  • not_found - (tùy chọn) Giá trị trả về nếu không tìm thấy kết quả phù hợp.
  • match_mode - (tùy chọn) 0 = khớp chính xác (mặc định), -1 = khớp chính xác hoặc khớp nhỏ nhất tiếp theo, 1 = khớp chính xác hoặc lớn hơn tiếp theo, 2 = khớp ký tự đại diện.
  • chế độ tìm kiếm - (tùy chọn) 1 = tìm kiếm từ đầu tiên (mặc định), -1 = tìm kiếm từ cuối cùng, 2 = tìm kiếm nhị phân tăng dần, -2 = tìm kiếm nhị phân giảm dần.

Phiên bản

Excel 365

Ghi chú sử dụng

XLOOKUP là một thay thế hiện đại cho hàm VLOOKUP. Đây là một chức năng linh hoạt và đa năng có thể được sử dụng trong nhiều trường hợp.

XLOOKUP có thể tìm các giá trị trong phạm vi dọc hoặc ngang, có thể thực hiện đối sánh gần đúng và chính xác, đồng thời hỗ trợ ký tự đại diện (*?) Cho các đối sánh từng phần. Ngoài ra, XLOOKUP có thể tìm kiếm dữ liệu bắt đầu từ giá trị đầu tiên hoặc giá trị cuối cùng (xem chi tiết loại đối sánh và chế độ tìm kiếm bên dưới). So với các hàm cũ hơn như VLOOKUP, HLOOKUP và LOOKUP, XLOOKUP cung cấp một số ưu điểm chính.

Không tìm thấy tin nhắn

Khi XLOOKUP không thể tìm thấy một kết quả phù hợp, nó sẽ trả về lỗi # N / A, giống như các hàm so khớp khác trong Excel. Không giống như các hàm đối sánh khác, XLOOKUP hỗ trợ đối số tùy chọn được gọi là not_found có thể được sử dụng để ghi đè lỗi # N / A khi nó xuất hiện. Các giá trị điển hình cho not_found có thể là "Không tìm thấy", "Không khớp", "Không có kết quả", v.v. Khi cung cấp giá trị cho not_found, hãy đặt văn bản trong dấu ngoặc kép ("").

Lưu ý: Hãy cẩn thận nếu bạn cung cấp một chuỗi rỗng ("") cho not_found. Nếu không tìm thấy kết quả phù hợp nào, XLOOKUP sẽ không hiển thị gì thay vì # N / A. Nếu bạn muốn thấy lỗi # N / A khi không tìm thấy kết quả phù hợp, hãy bỏ qua hoàn toàn đối số.

Loại so khớp

Theo mặc định, XLOOKUP sẽ thực hiện đối sánh chính xác. Hành vi đối sánh được kiểm soát bởi một đối số tùy chọn được gọi là match_type, có các tùy chọn sau:

Loại so khớp Hành vi
0 (mặc định) Kết hợp chuẩn xác. Sẽ trả về # N / A nếu không khớp.
-1 Đối sánh chính xác hoặc mục nhỏ hơn tiếp theo.
1 Đối sánh chính xác hoặc mục lớn hơn tiếp theo.
2 Đối sánh ký tự đại diện (*,?, ~)

Chế độ tìm kiếm

Theo mặc định, XLOOKUP sẽ bắt đầu khớp từ giá trị dữ liệu đầu tiên. Hành vi tìm kiếm được kiểm soát bởi một đối số tùy chọn được gọi là search_mode , cung cấp các tùy chọn sau:

Chế độ tìm kiếm Hành vi
1 (mặc định) Tìm kiếm từ giá trị đầu tiên
-1 Tìm kiếm từ giá trị cuối cùng (đảo ngược)
2 Các giá trị tìm kiếm nhị phân được sắp xếp theo thứ tự tăng dần
-2 Các giá trị tìm kiếm nhị phân được sắp xếp theo thứ tự giảm dần

Tìm kiếm nhị phân rất nhanh, nhưng dữ liệu phải được sắp xếp theo yêu cầu. Nếu dữ liệu không được sắp xếp đúng, tìm kiếm nhị phân có thể trả về kết quả không hợp lệ trông hoàn toàn bình thường.

Ví dụ số 1 - đối sánh chính xác cơ bản

Theo mặc định, XLOOKUP sẽ thực hiện đối sánh chính xác. Trong ví dụ dưới đây, XLOOKUP được sử dụng để truy xuất Doanh số dựa trên kết quả khớp chính xác trên Phim. Công thức của H5 là:

=XLOOKUP(H4,B5:B9,E5:E9)

Giải thích chi tiết hơn ở đây.

Ví dụ số 2 - đối sánh gần đúng cơ bản

Để kích hoạt đối sánh gần đúng, hãy cung cấp giá trị cho đối số "match_mode". Trong ví dụ bên dưới, XLOOKUP được sử dụng để tính chiết khấu dựa trên số lượng, yêu cầu giá trị khớp gần đúng. Công thức trong F5 cung cấp -1 cho match_mode để bật khớp gần đúng với hành vi "khớp chính xác hoặc nhỏ nhất tiếp theo":

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Giải thích chi tiết hơn ở đây.

Ví dụ # 3 - nhiều giá trị

XLOOKUP có thể trả về nhiều giá trị cùng một lúc cho cùng một kết quả khớp. Ví dụ dưới đây cho thấy cách XLOOKUP có thể được định cấu hình để trả về ba giá trị khớp với một công thức duy nhất. Công thức ở C5 là:

=XLOOKUP(B5,B8:B15,C8:E15)

Chú ý mảng trả về (C8: E15) gồm 3 cột: First, Last, Department. Tất cả ba giá trị được trả về và tràn vào phạm vi C5: E5.

Ví dụ # 4 - tra cứu hai chiều

XLOOKUP có thể được sử dụng để thực hiện tra cứu hai chiều, bằng cách lồng một XLOOKUP bên trong một XLOOKUP khác. Trong ví dụ dưới đây, XLOOKUP "bên trong" truy xuất toàn bộ một hàng (tất cả các giá trị cho Glass), được chuyển cho XLOOKUP "bên ngoài" dưới dạng mảng trả về. XLOOKUP bên ngoài tìm nhóm thích hợp (B) và trả về giá trị tương ứng (17,25) làm kết quả cuối cùng.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Thêm chi tiết tại đây.

Ví dụ # 5 - không tìm thấy thông báo

Giống như các hàm tra cứu khác, nếu XLOOKUP không tìm thấy giá trị, nó sẽ trả về lỗi # N / A. Để hiển thị thông báo tùy chỉnh thay vì # N / A, hãy cung cấp giá trị cho đối số "không tìm thấy" tùy chọn, được đặt trong dấu ngoặc kép (""). Ví dụ: để hiển thị "Không tìm thấy" khi không tìm thấy phim phù hợp, dựa trên trang tính bên dưới, hãy sử dụng:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Bạn có thể tùy chỉnh thông báo này theo ý muốn: "Không khớp", "Không tìm thấy phim", v.v.

Ví dụ số 6 - tiêu chí phức tạp

Với khả năng xử lý mảng nguyên bản, XLOOKUP có thể được sử dụng với các tiêu chí phức tạp. Trong ví dụ bên dưới, XLOOKUP khớp với bản ghi đầu tiên trong đó: tài khoản bắt đầu bằng "x" và vùng là "phía đông" và tháng không phải là tháng 4:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Chi tiết: (1) ví dụ đơn giản, (2) ví dụ phức tạp hơn.

Lợi ích của XLOOKUP

XLOOKUP cung cấp một số lợi thế quan trọng, đặc biệt là so với VLOOKUP:

  • XLOOKUP có thể tra cứu dữ liệu ở bên phải hoặc bên trái của các giá trị tra cứu
  • XLOOKUP có thể trả về nhiều kết quả (ví dụ # 3 ở trên)
  • XLOOKUP mặc định là đối sánh chính xác (VLOOKUP mặc định là giá trị gần đúng)
  • XLOOKUP có thể hoạt động với dữ liệu dọc và ngang
  • XLOOKUP có thể thực hiện tìm kiếm ngược (từ cuối đến trước)
  • XLOOKUP có thể trả về toàn bộ hàng hoặc cột, không chỉ một giá trị
  • XLOOKUP có thể làm việc với các mảng nguyên bản để áp dụng các tiêu chí phức tạp

Ghi chú

  1. XLOOKUP có thể hoạt động với cả mảng dọc và mảng ngang.
  2. XLOOKUP sẽ trả về # N / A nếu không tìm thấy giá trị tra cứu.
  3. Các lookup_array phải có một chiều hướng phù hợp với return_array lập luận, nếu không sẽ trở lại XLOOKUP #VALUE!
  4. Nếu XLOOKUP được sử dụng giữa các sổ làm việc, cả hai sổ làm việc phải mở, nếu không XLOOKUP sẽ trả về lỗi #REF !.
  5. Giống như hàm INDEX, kết quả là XLOOKUP trả về một tham chiếu.

Các video liên quan

Ví dụ cơ bản về XLOOKUP Trong video này, chúng tôi sẽ thiết lập hàm XLOOKUP với một ví dụ cơ bản. Đối sánh với tên Thành phố, chúng tôi sẽ truy xuất Quốc gia và Dân số. Đối sánh gần đúng XLOOKUP cơ bản Trong video này, chúng tôi sẽ thiết lập hàm XLOOKUP để thực hiện đối sánh gần đúng nhằm tính chiết khấu dựa trên số lượng. XLOOKUP với logic boolean Trong video này, chúng ta sẽ xem xét cách sử dụng hàm XLOOKUP với logic Boolean để áp dụng nhiều tiêu chí. XLOOKUP với nhiều giá trị tra cứu Trong video này, chúng tôi sẽ thiết lập XLOOKUP để trả về nhiều giá trị trong một mảng động, bằng cách cung cấp một loạt giá trị tra cứu thay vì một giá trị tra cứu duy nhất.

thú vị bài viết...