XLOOKUP làm được mọi việc của VLOOKUP, với ít chỗ để sai hơn: mặc định so khớp chính xác, nhận cột trả về dưới dạng một vùng thay vì một con số, tra cứu được sang trái và có sẵn đối số "không tìm thấy". Hãy dùng VLOOKUP khi tệp phải chạy trong Excel 2019 trở về trước, nơi chưa có XLOOKUP. Trang tính dưới đây chạy cùng một phép tra cứu theo cả hai cách.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Pear | |
| 2 | Apple | Fruit | $1.20 | 40 | VLOOKUP | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | XLOOKUP | $1.50 | |
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Cả hai đều trả về $1,50. Bấm vào từng công thức: VLOOKUP tô viền cả bảng A2:D6 và đếm 3 cột vào trong; XLOOKUP chỉ tô viền cột nó tìm kiếm và cột nó trả về.
So sánh VLOOKUP và XLOOKUP
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Phiên bản Excel | Tất cả | Excel 2021, 2024, Microsoft 365, web |
| Kiểu so khớp mặc định | Gần đúng (nếu bỏ trống đối số thứ 4) | Chính xác |
| Cột trả về | Một con số đếm vào trong bảng | Một vùng |
| Chèn cột vào giữa bảng | Trả về sai cột | Vẫn chạy đúng |
| Tra cứu sang trái | Không | Có |
| Không tìm thấy giá trị | #N/A, bọc trong IFNA | Đối số thứ 4, "Not found" |
| Kết quả khớp cuối cùng | Không | search_mode -1 |
| Nhiều cột cùng lúc | Mỗi công thức một cột (hoặc {2,3} làm số thứ tự cột trong Microsoft 365) | Vùng trả về gồm nhiều cột sẽ tràn ra tất cả |
| So khớp gần đúng | Giá trị nhỏ hơn kế tiếp, dữ liệu phải được sắp xếp | Giá trị nhỏ hơn hoặc lớn hơn kế tiếp, thứ tự bất kỳ |
| Ký tự đại diện | Bật khi dùng FALSE | Chỉ khi match_mode là 2 |
| Tra cứu theo hàng ngang | Cần HLOOKUP | Cùng một hàm |
Cả hai đều bỏ qua chữ hoa chữ thường. Khi so khớp chính xác, cả hai đều trả về kết quả khớp đầu tiên tính từ trên xuống, trừ khi XLOOKUP được yêu cầu tìm từ dưới lên.
Không tìm thấy: IFNA so với đối số thứ tư
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Kiwi | |
| 2 | Apple | Fruit | $1.20 | 40 | VLOOKUP | #N/A | |
| 3 | Pear | Fruit | $1.50 | 25 | with IFNA | Not found | |
| 4 | Carrot | Vegetable | $0.80 | 60 | XLOOKUP | Not found | |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A Giá trị bạn tìm không có trong vùng tra cứu.VLOOKUP thuần hiện #N/A. VLOOKUP cần bọc trong IFNA để hiện văn bản, còn XLOOKUP làm việc đó bằng đối số thứ tư. Gõ Milk vào G1 thì cả ba đều khớp nhau: $1,10.
Tra cứu sang trái và di chuyển cột
Hai giới hạn về cấu trúc của VLOOKUP đều đến từ số thứ tự cột. Hàm chỉ đếm được sang bên phải cột nó tìm kiếm, và con số này không đổi khi bảng thay đổi: chèn một cột vào giữa Category và Price, =VLOOKUP(G1,A2:D6,3,FALSE) vẫn trả về cột 3, giờ là cột mới. XLOOKUP tham chiếu cột trả về dưới dạng một vùng, nên Excel điều chỉnh nó như mọi tham chiếu khác, và cột tìm kiếm có thể nằm ở bất cứ đâu.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | $0.80 | |
| 2 | Apple | Fruit | $1.20 | 40 | XLOOKUP | Carrot | |
| 3 | Pear | Fruit | $1.50 | 25 | INDEX MATCH | Carrot | |
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Cả hai đều trả về Carrot. Không có phiên bản VLOOKUP nào cho việc này: tên sản phẩm nằm bên trái giá. Trong Excel cũ, lời giải là INDEX MATCH, như ở G3, cách này cũng không hỏng khi chèn cột. Trang INDEX và MATCH giải thích chi tiết.
So khớp gần đúng theo cả hai cách
Với các bậc giá trị, VLOOKUP dùng TRUE và cần cột đầu tiên được sắp xếp tăng dần. XLOOKUP dùng match_mode -1 và không cần sắp xếp, còn match_mode 1 cho giá trị lớn hơn kế tiếp, việc mà VLOOKUP không làm được.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sales from | Rate | Sales | 4,200 | |
| 2 | 0 | 0% | VLOOKUP | 3% | |
| 3 | 1000 | 3% | XLOOKUP | 3% | |
| 4 | 5000 | 5% | |||
| 5 | 10000 | 8% |
Cả hai đều trả về 3% cho 4.200. Đổi E1 thành 10000 thì cả hai trả về 8%.
Khi nào nên giữ VLOOKUP
- Tệp được chia sẻ với người dùng Excel 2019, 2016 hoặc cũ hơn. Ở đó XLOOKUP hiện #NAME?. INDEX MATCH là lựa chọn khác chạy được ở mọi nơi.
- Workbook đã có hàng trăm hàm VLOOKUP đang chạy tốt. Viết lại chúng chẳng được bao nhiêu; hãy dùng XLOOKUP cho các công thức mới.
Tốc độ không phải lý do để chọn hàm nào. Trên các trang tính thông thường, cả hai đều cho kết quả tức thì, còn với danh sách đã sắp xếp rất lớn, tìm kiếm nhị phân của XLOOKUP (search_mode 2) và VLOOKUP với TRUE đều nhanh.
Google Sheets hỗ trợ cả hai hàm với cùng các đối số.
Luyện tập: viết lại một hàm VLOOKUP
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | 40 | Stock | ||
| 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: =VLOOKUP(G1,A2:D6,4,FALSE) trả về số lượng tồn kho của sản phẩm trong G1. Trong G2, viết cùng phép tra cứu đó bằng XLOOKUP.
Câu hỏi thường gặp
XLOOKUP có tốt hơn VLOOKUP không?
Với công việc mới trong Excel 2021 hoặc Microsoft 365 thì có: hàm mặc định so khớp chính xác, không có số thứ tự cột dễ hỏng khi cột bị di chuyển, tra cứu được sang trái và có sẵn văn bản báo không tìm thấy. VLOOKUP chỉ tốt hơn khi tệp phải chạy trong Excel 2019 trở về trước, nơi XLOOKUP hiện #NAME?.
XLOOKUP có nhanh hơn VLOOKUP không?
Không tới mức bạn nhận ra trên các trang tính thông thường; cả hai đều tìm qua vài nghìn hàng ngay lập tức. Với dữ liệu đã sắp xếp rất lớn, chế độ tìm kiếm nhị phân của XLOOKUP (search_mode 2) nhanh hơn tìm tuần tự, và VLOOKUP với TRUE cũng tìm kiếm nhị phân.
XLOOKUP và INDEX MATCH khác nhau thế nào?
Hai cách làm được cùng các phép tra cứu. XLOOKUP là một hàm duy nhất, đối số đơn giản hơn và có đối số khi không tìm thấy; INDEX MATCH chạy trong mọi phiên bản Excel. =XLOOKUP(F2,A2:A6,C2:C6) và =INDEX(C2:C6,MATCH(F2,A2:A6,0)) trả về cùng một giá trị.
Làm sao chuyển VLOOKUP sang XLOOKUP?
Giữ nguyên giá trị cần tìm, tách bảng thành cột đầu tiên và cột cần trả về, rồi bỏ số thứ tự cột và FALSE: =VLOOKUP(F2,A2:D6,3,FALSE) thành =XLOOKUP(F2,A2:A6,C2:C6).
