Hàm XLOOKUP Trong Excel: Cách Dùng Và Nhiều Điều Kiện

XLOOKUP là hàm tra cứu thay thế cho VLOOKUP, khắc phục gần như toàn bộ những điểm khó chịu của hàm cũ: tra được sang trái, không cần đếm số thứ tự cột, và có sẵn chỗ để xử lý khi không tìm thấy.

Hàm này chỉ có từ Office 2021 trở lên. Nếu Excel của bạn báo lỗi #NAME? khi gõ XLOOKUP, nghĩa là phiên bản chưa hỗ trợ chứ không phải bạn gõ sai.

Cú pháp cơ bản

=XLOOKUP(giá_trị_tìm, vùng_tìm, vùng_trả_về, [nếu_không_thấy], [kiểu_khớp], [kiểu_duyệt])

Ba tham số đầu là bắt buộc, ba tham số sau tùy chọn. Ví dụ tra tên nhân viên theo mã:

=XLOOKUP(E2, A2:A100, B2:B100)

Đọc là: tìm giá trị ở ô E2 trong cột A, trả về giá trị tương ứng ở cột B. Không phải đếm cột, không phải thêm tham số FALSE ở cuối như VLOOKUP.

Xử lý khi không tìm thấy

Tham số thứ tư thay thế cho cách bọc IFERROR bên ngoài:

=XLOOKUP(E2, A2:A100, B2:B100, "Không tìm thấy")

Công thức ngắn hơn và chạy nhanh hơn, vì IFERROR bắt mọi loại lỗi còn tham số này chỉ xử lý đúng trường hợp không tìm thấy. Nhờ vậy nếu công thức có lỗi thật, bạn vẫn nhìn ra thay vì bị che đi.

Tra cứu sang trái

Đây là điểm VLOOKUP không làm được. Với XLOOKUP thì không có khái niệm trái phải, vì bạn chỉ định vùng tìm và vùng trả về một cách độc lập:

=XLOOKUP(E2, C2:C100, A2:A100)

Tìm trong cột C, trả về cột A. Trước đây việc này phải dùng INDEX kết hợp MATCH, xem INDEX MATCH và VLOOKUP.

Trả về nhiều cột cùng lúc

Vùng trả về có thể rộng hơn một cột. Khi đó XLOOKUP trả ra cả khối kết quả:

=XLOOKUP(E2, A2:A100, B2:D100)

Kết quả tự tràn sang ba ô liền nhau. Đây là cơ chế mảng động, nên nếu ô bên cạnh đang có dữ liệu thì bạn sẽ gặp lỗi #SPILL, cách xử lý ở bài mảng động và lỗi #SPILL trong Excel.

Tra theo nhiều điều kiện

XLOOKUP không có tham số riêng cho nhiều điều kiện, nhưng có hai cách làm.

Cách nhân điều kiện. Dùng khi các điều kiện là đúng hoặc sai:

=XLOOKUP(1, (A2:A100=E2)*(B2:B100=F2), C2:C100)

Mỗi biểu thức so sánh trả về 1 hoặc 0, nhân với nhau thì chỉ dòng thỏa cả hai điều kiện mới ra 1.

Cách nối chuỗi. Dễ đọc hơn nhưng chậm hơn với bảng lớn:

=XLOOKUP(E2&F2, A2:A100&B2:B100, C2:C100)

Khớp gần đúng và tìm ngược

Tham số thứ năm quyết định kiểu khớp. Giá trị 0 là khớp chính xác, đây cũng là mặc định. Giá trị -1 lấy giá trị nhỏ hơn gần nhất, giá trị 1 lấy giá trị lớn hơn gần nhất. Dùng cho các bảng bậc thang như biểu thuế hoặc bảng chiết khấu theo doanh số.

Tham số thứ sáu quyết định chiều duyệt. Giá trị -1 duyệt từ dưới lên, hữu ích khi bạn cần lấy bản ghi mới nhất trong danh sách có nhiều dòng trùng mã.

Khi nào vẫn nên dùng VLOOKUP

Chỉ một trường hợp: khi file sẽ được mở trên máy dùng Office 2016 hoặc 2019. Những máy đó không có XLOOKUP và sẽ hiện lỗi #NAME? ở mọi ô.

Nếu cả phòng dùng chung file, nên thống nhất một phiên bản Office để tránh mỗi máy thấy một kiểu. Bản có XLOOKUP thấp nhất là key Office 2021, so sánh đầy đủ giữa các bản tại so sánh Office 2016, 2019, 2021 và 2024.

Câu hỏi thường gặp

XLOOKUP có nhanh hơn VLOOKUP không?

Với bảng lớn thì thường nhanh hơn, vì XLOOKUP chỉ đọc hai cột được chỉ định thay vì quét cả khối như VLOOKUP. Đây cũng là một cách giảm tải cho file nặng, xem tối ưu file Excel nặng.

Gửi file cho người dùng bản cũ thì làm sao?

Sao chép vùng kết quả rồi dán lại dưới dạng giá trị trước khi gửi. Người nhận thấy số đúng, chỉ là không tự cập nhật được.

XLOOKUP báo lỗi #N/A dù dữ liệu có tồn tại?

Thường do khác kiểu dữ liệu: một bên là số, một bên là văn bản trông giống số. Hoặc do khoảng trắng thừa ở cuối chuỗi, dùng hàm TRIM để loại bỏ.