Cách Sử Dụng Hàm XLOOKUP Trong Excel 2021 Với 8 Ví Dụ Thực Tế

Hàm VLOOKUP chỉ tìm kiếm từ trái sang phải, bắt buộc phải nhớ số thứ tự cột, và trả về lỗi khó hiểu khi không tìm thấy dữ liệu. Hàm XLOOKUP trong Excel 2021 giải quyết tất cả những hạn chế này với cú pháp đơn giản hơn và khả năng tìm kiếm theo mọi hướng. Sau khi chuyển sang XLOOKUP, thời gian xây dựng công thức của tôi giảm từ 5 phút xuống còn 30 giây.

Mục Lục

XLOOKUP Là Gì Và Tại Sao Nó Tốt Hơn

Hàm XLOOKUP được Microsoft phát hành cho Excel 365 và Excel 2021 để thay thế hoàn toàn VLOOKUP và HLOOKUP. Thay vì ghi nhớ số thứ tự cột hoặc phải sắp xếp dữ liệu theo thứ tự nhất định, XLOOKUP cho phép bạn chọn trực tiếp vùng tìm kiếm và vùng trả về kết quả.

Ưu điểm vượt trội:

Tìm kiếm theo mọi hướng: trái, phải, trên, dưới
Mặc định tìm kiếm chính xác, không cần tham số TRUE/FALSE như VLOOKUP
Có tham số xử lý lỗi tích hợp sẵn, không cần kết hợp với IFERROR
Trả về nhiều cột cùng lúc trong một công thức duy nhất
Cú pháp đơn giản với chỉ 3 tham số bắt buộc

Trước đây, tôi phải viết công thức VLOOKUP dài 2 dòng với INDEX-MATCH để tìm kiếm sang trái. Giờ đây, XLOOKUP giải quyết trong một công thức ngắn gọn.

Cú Pháp Hàm XLOOKUP

Cú pháp đầy đủ của hàm có 6 tham số, nhưng chỉ 3 tham số đầu là bắt buộc:

=XLOOKUP(giá_trị_tìm_kiếm, vùng_tìm_kiếm, vùng_trả_về, [nếu_không_tìm_thấy], [chế_độ_khớp], [chế_độ_tìm_kiếm])

Các tham số bắt buộc:

giá_trị_tìm_kiếm: Giá trị bạn muốn tìm, có thể là số, văn bản hoặc tham chiếu ô
vùng_tìm_kiếm: Phạm vi ô chứa giá trị cần tìm, phải là một cột hoặc một hàng duy nhất
vùng_trả_về: Phạm vi ô chứa kết quả muốn lấy, kích thước phải tương ứng với vùng tìm kiếm

Các tham số tùy chọn:

nếu_không_tìm_thấy: Giá trị hiển thị khi không tìm thấy kết quả (mặc định là lỗi #N/A)
chế_độ_khớp: 0 = chính xác (mặc định), -1 = chính xác hoặc nhỏ hơn, 1 = chính xác hoặc lớn hơn, 2 = ký tự đại diện
chế_độ_tìm_kiếm: 1 = từ đầu xuống cuối (mặc định), -1 = từ cuối lên đầu

Ví Dụ 1: Tra Cứu Cơ Bản Thay Thế VLOOKUP

Tìm thông tin nhân viên dựa trên mã số

Giả sử bạn có bảng dữ liệu nhân viên với mã số ở cột A, tên ở cột B, và phòng ban ở cột C. Bạn muốn tìm tên nhân viên có mã số NV105.

XEM THÊM: Dừng Đếm Cột Bằng Tay - Hàm COLUMN Tự Động Hóa Mọi Thứ

Các bước thực hiện:

Nhập mã số cần tìm vào ô F2: NV105
Tại ô G2, nhập công thức: =XLOOKUP(F2,A2:A50,B2:B50)
Nhấn Enter để xem kết quả

Công thức này tìm giá trị trong F2 (NV105) ở vùng A2:A50, sau đó trả về giá trị tương ứng từ vùng B2:B50. So với VLOOKUP, bạn không cần đếm số thứ tự cột hoặc chọn toàn bộ bảng dữ liệu.

Ví Dụ 2: Tìm Kiếm Từ Phải Sang Trái

Tìm mã sản phẩm dựa trên tên sản phẩm

Một hạn chế lớn của VLOOKUP là chỉ tìm sang phải. Nếu cột kết quả nằm bên trái cột tìm kiếm, VLOOKUP không thể hoạt động. XLOOKUP giải quyết vấn đề này hoàn toàn.

Giả sử mã sản phẩm ở cột A, tên sản phẩm ở cột B, và bạn muốn tìm mã sản phẩm dựa trên tên.

Công thức:

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

Ở đây, vùng tìm kiếm (B2:B100) nằm bên phải vùng trả về (A2:A100), điều hoàn toàn không thể với VLOOKUP. Trước đây, tôi phải dùng INDEX-MATCH hoặc sắp xếp lại cột dữ liệu, mất thêm 3-4 phút mỗi lần.

Ví Dụ 3: Xử Lý Lỗi Với Tham Số Tùy Chọn

Hiển thị thông báo thân thiện thay vì lỗi #N/A

Khi VLOOKUP không tìm thấy giá trị, nó trả về lỗi #N/A khó hiểu. Bạn phải kết hợp với hàm IFERROR để xử lý. XLOOKUP có tham số thứ tư để giải quyết vấn đề này ngay trong công thức.

Công thức với xử lý lỗi:

=XLOOKUP(F2,A2:A50,B2:B50,"Không tìm thấy nhân viên")

Nếu mã nhân viên trong F2 không tồn tại, thay vì hiển thị #N/A, ô sẽ hiển thị văn bản “Không tìm thấy nhân viên”. Điều này giúp bảng tính chuyên nghiệp hơn và dễ đọc hơn cho người dùng cuối.

Ứng dụng thực tế: Tôi dùng tham số này trong bảng tra cứu giá sản phẩm. Khi nhân viên bán hàng nhập mã sản phẩm không đúng, thay vì thấy lỗi, họ nhìn thấy “Kiểm tra lại mã sản phẩm” và biết ngay phải làm gì.

Ví Dụ 4: Tra Cứu Hai Chiều

Tìm doanh số theo cả nhân viên và tháng

XLOOKUP có thể lồng nhau để thực hiện tra cứu theo hai chiều, giống như kết hợp INDEX-MATCH nhưng đơn giản hơn nhiều.

Giả sử bạn có bảng doanh số với tên nhân viên ở cột A, các tháng ở hàng đầu tiên (từ B1 đến M1), và doanh số tương ứng bên trong bảng.

Công thức tra cứu hai chiều:

=XLOOKUP(E2,A2:A20,XLOOKUP(F2,B1:M1,B2:M20))

Trong công thức này:

E2 chứa tên nhân viên cần tìm
F2 chứa tháng cần tìm
XLOOKUP bên trong tìm cột tháng phù hợp
XLOOKUP bên ngoài tìm hàng nhân viên phù hợp
Kết quả là giá trị tại giao điểm của cả hai

XEM THÊM: Hàm SORT Excel 2021: Cách Sắp Xếp Dữ Liệu Nhanh Gấp 30 Lần Phương Pháp Cũ

So với công thức INDEX-MATCH tương đương dài 15 ký tự hơn, XLOOKUP dễ đọc và dễ sửa lỗi hơn rất nhiều.

Ví Dụ 5: Trả Về Nhiều Cột Cùng Lúc

Lấy cả tên và phòng ban của nhân viên trong một công thức

VLOOKUP chỉ trả về một cột mỗi lần, buộc bạn phải viết nhiều công thức riêng. XLOOKUP có thể trả về nhiều cột liền kề chỉ bằng một công thức duy nhất.

Công thức trả về nhiều cột:

=XLOOKUP(F2,A2:A50,B2:D50)

Công thức này tìm mã nhân viên trong F2, sau đó trả về toàn bộ 3 cột từ B đến D (tên, phòng ban, và chức vụ). Kết quả tự động lan ra 3 ô liền kề bên phải ô công thức.

Lưu ý quan trọng: Đảm bảo 3 ô bên phải ô công thức đang trống. Nếu có dữ liệu, Excel sẽ báo lỗi #SPILL. Tính năng này gọi là dynamic array, chỉ có trong Excel 365 và Excel 2021.

Ví Dụ 6: Tra Cứu Gần Đúng Cho Bảng Thuế

Tìm mức thuế áp dụng dựa trên thu nhập

Tra cứu gần đúng hữu ích khi làm việc với các khoảng giá trị, như bảng thuế lũy tiến hoặc chiết khấu theo số lượng.

Giả sử bảng thuế có cột A chứa ngưỡng thu nhập (0, 5000000, 10000000, 18000000…) và cột B chứa mức thuế tương ứng (5%, 10%, 15%, 20%…).

Công thức tra cứu gần đúng:

=XLOOKUP(E2,A2:A10,B2:B10,,-1)

Tham số thứ 5 là -1, nghĩa là tìm giá trị chính xác hoặc giá trị nhỏ hơn gần nhất. Nếu thu nhập trong E2 là 12000000, công thức sẽ tìm ngưỡng 10000000 và trả về mức thuế 15%.

Điều kiện quan trọng: Dữ liệu trong cột tìm kiếm phải được sắp xếp theo thứ tự tăng dần khi dùng chế độ khớp -1, hoặc giảm dần khi dùng chế độ khớp 1.

Ví Dụ 7: Tìm Kiếm Với Ký Tự Đại Diện

Tìm sản phẩm khi chỉ nhớ một phần tên

Khi bạn không nhớ chính xác tên sản phẩm hoặc cần tìm kiếm theo mẫu, ký tự đại diện rất hữu ích. XLOOKUP hỗ trợ hai loại ký tự đại diện: dấu sao (*) đại diện cho nhiều ký tự bất kỳ, và dấu hỏi (?) đại diện cho một ký tự đơn.

Công thức với ký tự đại diện:

=XLOOKUP("*Pro*",A2:A100,B2:B100,,2)

Tham số thứ 5 là 2, kích hoạt chế độ tìm kiếm ký tự đại diện. Công thức này sẽ tìm bất kỳ sản phẩm nào có chữ “Pro” trong tên, ví dụ: “iPhone Pro”, “MacBook Pro”, “Surface Pro”.

Ví dụ với dấu hỏi:

=XLOOKUP("SP-???",A2:A100,B2:B100,,2)

Công thức này tìm mã sản phẩm bắt đầu bằng “SP-” theo sau là đúng 3 ký tự bất kỳ, như “SP-001”, “SP-ABC”, “SP-X12”.

Ví Dụ 8: Tìm Giá Trị Xuất Hiện Cuối Cùng

Lấy giao dịch mới nhất của khách hàng

Trong bảng giao dịch có nhiều bản ghi cho cùng một khách hàng, bạn muốn lấy thông tin giao dịch mới nhất. VLOOKUP luôn trả về kết quả đầu tiên tìm thấy, buộc bạn phải sắp xếp lại dữ liệu. XLOOKUP có tham số tìm kiếm từ cuối lên đầu.

XEM THÊM: Tôi Thay Pivot Table Bằng Hàm GROUPBY Và Tiết Kiệm 3 Giờ Mỗi Tuần

Công thức tìm từ dưới lên:

=XLOOKUP(F2,A2:A1000,D2:D1000,,-1)

Tham số thứ 6 là -1, hướng dẫn XLOOKUP bắt đầu tìm từ hàng cuối cùng (A1000) lên hàng đầu tiên (A2). Khi tìm thấy kết quả khớp đầu tiên trong hướng này, hàm dừng lại và trả về giá trị tương ứng – đó chính là giao dịch mới nhất.

Ứng dụng thực tế: Tôi dùng kỹ thuật này trong bảng theo dõi tồn kho. Mỗi lần nhập hoặc xuất hàng tạo một dòng mới. Để biết số lượng tồn hiện tại của sản phẩm, tôi dùng XLOOKUP với chế độ tìm ngược để lấy bản ghi mới nhất.

So Sánh XLOOKUP Với VLOOKUP

Để thấy rõ sự khác biệt, đây là cùng một bài toán được giải bằng cả hai hàm:

Bài toán: Tìm giá sản phẩm có mã SP-105 trong bảng có mã ở cột B và giá ở cột E.

Với VLOOKUP:

=VLOOKUP(F2,B2:E100,4,FALSE)

Phải đếm E là cột thứ 4 trong vùng B:E, phải thêm FALSE cho tìm kiếm chính xác, không thể tìm nếu giá nằm bên trái mã.

Với XLOOKUP:

=XLOOKUP(F2,B2:B100,E2:E100)

Chọn trực tiếp cột mã và cột giá, mặc định tìm chính xác, hoạt động dù giá ở bên trái hay phải mã.

Hiệu suất của hai hàm tương đương nhau trên các bảng dưới 10000 hàng. Với dữ liệu lớn hơn, XLOOKUP nhanh hơn 15-20% nhờ cơ chế tìm kiếm được tối ưu hóa.

Khi Nào Nên Dùng XLOOKUP

XLOOKUP phù hợp nhất trong các tình huống sau:

Làm việc với bảng dữ liệu có cấu trúc thay đổi thường xuyên
Cần tìm kiếm theo nhiều hướng khác nhau
Muốn giảm độ phức tạp của công thức
Xây dựng báo cáo tự động cập nhật
Chia sẻ file với người không chuyên về Excel

Tuy nhiên, nếu file cần tương thích ngược với Excel 2019 hoặc cũ hơn, bạn buộc phải dùng VLOOKUP hoặc INDEX-MATCH vì XLOOKUP sẽ hiển thị lỗi #NAME trên các phiên bản đó.

Yêu Cầu Và Khả Năng Tương Thích

Hàm XLOOKUP chỉ khả dụng trong các phiên bản sau:

Microsoft 365 (Windows và Mac) – tất cả các gói đăng ký
Excel 2021 (Windows và Mac) – bản mua đứt độc lập
Excel cho web – miễn phí với tài khoản Microsoft

Không hỗ trợ trong Excel 2019, Excel 2016, hoặc các phiên bản cũ hơn. Nếu mở file chứa XLOOKUP trên các phiên bản này, công thức sẽ hiển thị lỗi #NAME.

Kiểm tra phiên bản Excel: Vào File > Tài khoản > Giới thiệu về Excel để xem phiên bản chính xác. Nếu thấy số phiên bản bắt đầu bằng 16.0.14326 trở lên, bạn có thể dùng XLOOKUP.

Lời Kết

Hàm XLOOKUP đơn giản hóa việc tra cứu dữ liệu trong Excel với cú pháp rõ ràng và tính linh hoạt vượt trội. Từ tra cứu cơ bản đến các tình huống phức tạp như tìm kiếm hai chiều hay trả về nhiều cột, XLOOKUP xử lý tất cả chỉ trong một công thức ngắn gọn. Nếu đang dùng Excel 365 hoặc Excel 2021, hãy bắt đầu thay thế VLOOKUP bằng XLOOKUP trong các báo cáo mới để làm quen với cú pháp. Ba tham số đầu tiên là đủ cho 80% tình huống sử dụng hàng ngày.