=XLOOKUP(F2,A2:A6,C2:C6) tìm giá trị trong F2 ở A2:A6 và trả về giá trị ở cùng hàng của C2:C6. Mặc định hàm tìm kết quả khớp chính xác, cột tìm kiếm có thể nằm ở bất cứ đâu, và hàm cần Excel 2021 hoặc Microsoft 365 (với Excel 2019 trở về trước, hãy dùng INDEX và MATCH). Thử gõ một sản phẩm khác vào F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Bread | $2.40 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Bấm vào G2: vùng tìm kiếm và vùng trả về được tô viền riêng. Đổi C2:C6 thành B2:B6 thì G2 trả về danh mục. Không có số thứ tự cột nào phải đếm, nên chèn một cột vào giữa A và C không làm hỏng công thức: Excel tự dịch chuyển cả hai vùng.
Cú pháp XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Đối số | Tác dụng | Mặc định |
|---|---|---|
lookup_value | Giá trị cần tìm. | bắt buộc |
lookup_array | Cột (hoặc hàng) để tìm kiếm. | bắt buộc |
return_array | Cột, hàng hoặc khối ô để lấy kết quả. Cùng chiều cao với lookup_array. | bắt buộc |
if_not_found | Nội dung hiển thị khi không có gì khớp. | #N/A |
match_mode | 0 chính xác, -1 chính xác hoặc nhỏ hơn kế tiếp, 1 chính xác hoặc lớn hơn kế tiếp, 2 ký tự đại diện. | 0 |
search_mode | 1 từ đầu đến cuối, -1 từ cuối lên đầu, 2 và -2 tìm kiếm nhị phân trên dữ liệu đã sắp xếp. | 1 |
Chỉ ba đối số đầu là bắt buộc. Để bỏ qua một đối số tùy chọn và đặt một đối số phía sau, hãy để trống nó giữa hai dấu phẩy: =XLOOKUP(F2,A2:A6,C2:C6,,0,-1) đặt search_mode và để if_not_found ở giá trị mặc định.
Trả về nhiều cột cùng lúc
Đưa cho XLOOKUP một vùng trả về rộng nhiều cột, bạn sẽ nhận lại cả hàng. Kết quả tràn sang các ô bên cạnh công thức.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Category | Price | Stock |
| 2 | Apple | Fruit | $1.20 | 40 |
| 3 | Pear | Fruit | $1.50 | 25 |
| 4 | Carrot | Vegetable | $0.80 | 60 |
| 5 | Bread | Bakery | $2.40 | 15 |
| 6 | Milk | Dairy | $1.10 | 30 |
| 7 | Look for | Carrot | ||
| 8 | Result | Vegetable | $0.80 | 60 |
Một công thức trong B8 điền B8:D8 bằng Vegetable, $0,80 và 60. Gõ gì đó vào C8 thì B8 hiện #SPILL!, vì kết quả không còn chỗ; xóa nó đi thì kết quả quay lại. Để trả về các cột theo thứ tự khác, bọc vùng trả về trong CHOOSECOLS: =XLOOKUP(B7,A2:A6,CHOOSECOLS(B2:D6,3,1)) cho ra Stock, rồi đến Category.
XLOOKUP sang trái, và thông báo khi không khớp
Cột tìm kiếm không cần đứng đầu. Ở đây XLOOKUP tìm trong cột giá C và trả về tên sản phẩm ở cột A, việc mà VLOOKUP không làm được. Đối số thứ tư cho biết cần hiện gì khi không có sản phẩm nào có mức giá đó.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | Product | |
| 2 | Apple | Fruit | $1.20 | 40 | $2.40 | Bread | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
$2,40 trả về Bread. Đổi F2 thành 3 thì G2 hiện "No product" thay vì #N/A. "" ở đối số thứ tư cho ra một ô trông như trống. if_not_found chỉ xử lý trường hợp "không tìm thấy": một vùng trả về sai chiều cao vẫn cho lỗi #VALUE!, và đó là điều bạn muốn thấy.
Tìm kết quả khớp cuối cùng
XLOOKUP trả về kết quả khớp đầu tiên tính từ trên xuống. Đặt search_mode, đối số thứ sáu, là -1 thì hàm tìm từ dưới lên, nên trả về kết quả khớp cuối cùng: đơn hàng mới nhất, mức giá gần đây nhất, trạng thái sau cùng.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | First | Last | |
| 2 | 2026-03-02 | Ben | 120 | Ben | 120 | 60 | |
| 3 | 2026-03-05 | Ana | 80 | ||||
| 4 | 2026-03-09 | Ben | 45 | ||||
| 5 | 2026-03-12 | Cara | 200 | ||||
| 6 | 2026-03-20 | Ben | 60 | ||||
| 7 | 2026-03-24 | Ana | 95 |
Đơn hàng đầu tiên của Ben là 120 và đơn cuối cùng là 60. Đổi E2 thành Ana: 80 và 95. Cách này dựa vào việc các hàng được xếp theo thứ tự ngày. Nếu không, hãy tra cứu ngày mới nhất của khách hàng: =XLOOKUP(1,(B2:B7=E2)*(A2:A7=MAXIFS(A2:A7,B2:B7,E2)),C2:C7).
So khớp gần đúng: nhỏ hơn kế tiếp hoặc lớn hơn kế tiếp
match_mode -1 trả về kết quả khớp chính xác, hoặc nếu không có thì trả về giá trị nhỏ hơn kế tiếp. Đó là quy tắc cho các bậc: mức hoa hồng, bậc thuế, xếp loại điểm. Không giống VLOOKUP với TRUE, bảng không cần được sắp xếp. Các bậc bên dưới được cố ý xếp không theo thứ tự nào.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 5000 | 5% | Ana | 750 | 0% | |
| 3 | 0 | 0% | Ben | 4,200 | 3% | |
| 4 | 10000 | 8% | Cara | 5,000 | 5% | |
| 5 | 1000 | 3% | Dev | 12,500 | 8% |
Con số 4.200 của Ben nằm giữa 1.000 và 5.000, nên Ben nhận 3% của bậc 1.000. Con số 5.000 của Cara khớp chính xác, 5%. match_mode 1 làm theo chiều ngược lại, chính xác hoặc lớn hơn kế tiếp, để trả lời những câu như "chiếc hộp nhỏ nhất vừa đủ" hay "khung giờ giao hàng kế tiếp": =XLOOKUP(18,{5;12;25;50},{"S";"M";"L";"XL"},,1) trả về L.
XLOOKUP với ký tự đại diện
match_mode 2 biến * (số ký tự bất kỳ) và ? (một ký tự) thành ký tự đại diện. Nếu không có nó, XLOOKUP tìm đúng các ký tự đó theo nghĩa đen, ngược với VLOOKUP (chế độ so khớp chính xác của VLOOKUP chấp nhận ký tự đại diện), và đó thường là lý do một hàm XLOOKUP dùng ký tự đại diện trả về #N/A hoặc văn bản if_not_found:
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None") None: no product is named *coffee*
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None",2) 2.9, the price of Iced coffee
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
"coffee" tìm thấy Iced coffee trước, 8,50. Như mọi phép tra cứu trong Excel, so khớp bỏ qua chữ hoa chữ thường. Để tìm một dấu sao hoặc dấu hỏi thật trong match_mode 2, hãy đặt dấu ngã trước nó: "~*".
XLOOKUP hai chiều
Một hàm XLOOKUP trả về cả hàng có thể làm vùng trả về cho một hàm XLOOKUP thứ hai. Hàm bên trong chọn hàng theo khu vực, hàm bên ngoài chọn cột tháng từ hàng đó.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | South | ||
| 7 | Month | Feb | ||
| 8 | Sales | 3,600 |
XLOOKUP(B6,A2:A5,B2:D5) trả về hàng của South, gồm 3100, 3600 và 3300. Hàm XLOOKUP bên ngoài tìm Feb trong B1:D1 và lấy giá trị tương ứng từ hàng đó: 3.600. Hãy chọn khu vực và tháng khác ở B6 và B7. Phiên bản INDEX và MATCH của cùng phép tra cứu này có ở trang INDEX và MATCH.
XLOOKUP trong Excel cũ và Google Sheets
XLOOKUP có trong Excel 2021, Excel 2024, Microsoft 365, Excel cho web và các ứng dụng di động. Nếu bạn mở một tệp dùng hàm này trong Excel 2019 trở về trước, các công thức hiện #NAME? ngay khi chúng tính lại. Khi tệp phải chạy ở mọi nơi, hãy viết phép tra cứu bằng INDEX và MATCH, phiên bản nào cũng hiểu:
=XLOOKUP(F2, A2:A6, C2:C6, "Not found")
=IFNA(INDEX(C2:C6, MATCH(F2, A2:A6, 0)), "Not found")
Google Sheets có XLOOKUP từ năm 2022, với cùng các đối số. Để xem các điểm khác nhau đặt cạnh nhau, xem VLOOKUP và XLOOKUP. Để so khớp trên hai cột cùng lúc (sản phẩm và kích cỡ, tên và ngày), mẫu =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6) được giải thích ở trang tra cứu nhiều điều kiện.
Luyện tập: một mức giá, hoặc "Not found"
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | ||
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Đến lượt bạn: Trong G2, trả về giá của sản phẩm trong F2, hoặc văn bản Not found khi sản phẩm không có trong danh sách.
Luyện tập: chiết khấu theo giá trị đơn hàng
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order from | Discount | Order | Discount | |
| 2 | $0 | 0% | $320 | ||
| 3 | $100 | 5% | |||
| 4 | $250 | 10% | |||
| 5 | $500 | 15% |
Đến lượt bạn: Mỗi mức chiết khấu áp dụng từ giá trị đơn hàng của nó trở lên. Trong E2, dùng XLOOKUP để trả về mức chiết khấu cho giá trị đơn hàng trong D2.
Câu hỏi thường gặp
Cách dùng hàm XLOOKUP trong Excel?
Đưa vào ba đối số: giá trị cần tìm, cột để tìm kiếm và cột để trả về. =XLOOKUP("Pear",A2:A6,C2:C6) tìm Pear trong A2:A6 và trả về giá trị ở cùng hàng của C2:C6. Hàm tìm kết quả khớp chính xác, trừ khi bạn chỉ định khác.
Những phiên bản Excel nào có XLOOKUP?
Excel 2021, Excel 2024, Microsoft 365 và Excel cho web. Trong Excel 2019 trở về trước, công thức hiện #NAME?; ở đó hãy dùng =INDEX(C2:C6,MATCH(F2,A2:A6,0)). Google Sheets cũng có XLOOKUP.
Làm sao để XLOOKUP trả về ô trống hoặc văn bản thay vì #N/A?
Dùng đối số thứ tư, if_not_found: =XLOOKUP(F2,A2:A6,C2:C6,"Not found"), hoặc "" để có một ô trông như trống. Đối số này chỉ thay thế trường hợp không tìm thấy; các lỗi khác vẫn hiện ra.
Làm sao tìm kết quả khớp cuối cùng bằng XLOOKUP?
Đặt đối số thứ sáu, search_mode, là -1 để tìm từ dưới lên: =XLOOKUP("Ben",B2:B7,C2:C7,,0,-1) trả về số tiền cuối cùng của Ben thay vì số tiền đầu tiên.
XLOOKUP có trả về nhiều hơn một cột được không?
Có. Đưa cho hàm một vùng trả về rộng nhiều cột, như =XLOOKUP(F2,A2:A6,B2:D6), và kết quả sẽ tràn sang các ô bên cạnh. Các ô bị tràn vào phải trống, nếu không Excel hiện #SPILL!.
