Hàm XLOOKUP tìm một giá trị trong một cột hoặc dải ô, rồi trả về kết quả ở cùng hàng trong một cột khác, bất kể cột trả về nằm bên trái hay bên phải. Cú pháp cơ bản là =XLOOKUP(giá trị tìm, mảng tìm kiếm, mảng trả về), và XLOOKUP mặc định trả về kết quả khớp chính xác. Hàm này không có trong Excel 2016 và Excel 2019.
Mình hay gọi XLOOKUP là VLOOKUP đã sửa hết những điểm gây khó chịu. Bạn không cần đếm số cột, không cần đặt cột tra cứu ở ngoài cùng bên trái, và không lo quên đối số cuối rồi nhận về số sai. Bài này đi theo trang trợ giúp tiếng Việt của Microsoft mà mình đọc vào tháng 10 năm 2026.
Trước khi bắt đầu, một điều cần kiểm tra: phiên bản Excel của bạn. XLOOKUP không sẵn dùng trong Excel 2016 và Excel 2019. Bạn vẫn có thể gặp một sổ làm việc có XLOOKUP khi mở trên các phiên bản này, nếu người khác tạo tệp đó bằng Excel mới hơn. Nếu bạn gửi tệp cho đồng nghiệp còn dùng bản cũ, hãy hỏi trước.
Cú pháp XLOOKUP và sáu đối số
Cú pháp đầy đủ: =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode). Ba đối số đầu là phần bạn dùng hằng ngày, ba đối số sau là tùy chọn.
| Đối số | Bắt buộc? | Ý nghĩa |
|---|---|---|
| lookup_value | Bắt buộc | Giá trị để tìm kiếm. Nếu bỏ qua, XLOOKUP trả về các ô trống nó tìm thấy trong lookup_array |
| lookup_array | Bắt buộc | Mảng hoặc dải ô cần tìm kiếm |
| return_array | Bắt buộc | Mảng hoặc dải ô cần trả về |
| if_not_found | Tùy chọn | Văn bản trả về khi không tìm thấy kết quả khớp hợp lệ. Thiếu đối số này thì trả về #N/A |
| match_mode | Tùy chọn | Kiểu khớp: 0, -1, 1 hoặc 2 |
| search_mode | Tùy chọn | Chế độ tìm kiếm: 1, -1, 2 hoặc -2 |
XLOOKUP tìm trong một dải ô hoặc một mảng, rồi trả về mục tương ứng với giá trị khớp đầu tiên tìm được.
Khác biệt lớn nhất so với VLOOKUP
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Cách chỉ định cột trả về | Một mảng bảng duy nhất kèm số chỉ mục cột | Mảng tìm kiếm và mảng trả về riêng biệt |
| Hướng tra cứu | Chỉ từ trái sang phải | Mọi hướng: cột trả về có thể nằm bên trái |
| Kiểu khớp mặc định | Gần đúng (TRUE) nếu bỏ trống | Chính xác |
| Khi không tìm thấy | #N/A | #N/A, hoặc văn bản bạn đặt trong if_not_found |
| Trả về nhiều cột | Không | Có, một công thức trả về được nhiều mục |
| Phiên bản | Có trong Excel 2016 và 2019 | Không có trong Excel 2016 và 2019 |
Microsoft minh hoạ bằng ví dụ tra mã quốc gia: với XLOOKUP, công thức gồm ô F2, phạm vi B2:B11 và phạm vi D2:D11. Công thức VLOOKUP tương đương phải viết là =VLOOKUP(F2,B2:D11,3,FALSE), tức bạn phải đếm cột và nhớ ghi FALSE. Với XLOOKUP, bạn chỉ trỏ thẳng vào cột cần lấy.
Các bước viết công thức XLOOKUP
- Chọn ô cần hiện kết quả và gõ =XLOOKUP(
- Chọn ô chứa giá trị cần tìm, ví dụ F2.
- Chọn cột để tìm kiếm, ví dụ B2:B11.
- Chọn cột chứa kết quả muốn lấy, ví dụ D2:D11.
- Nếu muốn, thêm văn bản hiển thị khi không tìm thấy, ví dụ "Không tìm thấy", rồi đóng ngoặc và nhấn Enter.
Kết quả là =XLOOKUP(F2,B2:B11,D2:D11). Không cần match_mode, vì XLOOKUP tạo kết quả khớp chính xác theo mặc định.
match_mode: bốn kiểu khớp
| Giá trị | Cách hoạt động |
|---|---|
| 0 | Khớp chính xác. Không tìm thấy thì trả về #N/A. Đây là mặc định |
| -1 | Khớp chính xác; nếu không có, trả về mục nhỏ hơn tiếp theo |
| 1 | Khớp chính xác; nếu không có, trả về mục lớn hơn tiếp theo |
| 2 | Khớp ký tự đại diện, trong đó *, ? và ~ có ý nghĩa đặc biệt |
Giá trị -1 và 1 là thứ thay thế cho kiểu khớp gần đúng của VLOOKUP, nhưng rõ ràng hơn: bạn tự quyết định lấy mục nhỏ hơn hay lớn hơn tiếp theo.
Tìm theo ký tự đại diện với match_mode 2
Khác với VLOOKUP, XLOOKUP không tự hiểu ký tự đại diện. Bạn phải đặt match_mode là 2 thì các ký tự *, ? và ~ mới mang ý nghĩa đặc biệt. Theo cách Microsoft giải thích cho các hàm tra cứu, dấu sao (*) khớp với một chuỗi ký tự bất kỳ, dấu chấm hỏi (?) khớp với một ký tự đơn lẻ bất kỳ, còn dấu sóng (~) đặt trước * hoặc ? khi bạn cần tìm đúng ký tự đó. Ví dụ, với match_mode 2, giá trị tìm "Fontan?" sẽ khớp với Fontana. Nếu bạn để match_mode mặc định là 0, cùng giá trị đó chỉ tìm đúng chuỗi có dấu chấm hỏi.
search_mode: tìm từ đầu, từ cuối hay tìm nhị phân
| Giá trị | Cách hoạt động |
|---|---|
| 1 | Tìm từ mục đầu tiên. Đây là mặc định |
| -1 | Tìm đảo ngược, bắt đầu từ mục cuối cùng |
| 2 | Tìm nhị phân, lookup_array phải sắp xếp tăng dần; không sắp xếp thì kết quả không hợp lệ |
| -2 | Tìm nhị phân, lookup_array phải sắp xếp giảm dần; không sắp xếp thì kết quả không hợp lệ |
Chế độ -1 rất tiện khi danh sách có giá trị lặp lại và bạn muốn lấy lần xuất hiện cuối cùng, ví dụ đơn hàng gần nhất của một khách. Chế độ 2 và -2 chỉ nên dùng khi bạn chắc chắn dữ liệu đã được sắp xếp.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Sáu ví dụ XLOOKUP từ Microsoft
- Ví dụ 1: tra tên quốc gia trong B2:B11 và trả về mã quốc gia qua điện thoại trong D2:D11, dựa vào ô F2.
- Ví dụ 2: tra thông tin nhân viên theo số ID. Không giống VLOOKUP, XLOOKUP trả về được một mảng có nhiều mục, nên một công thức duy nhất trả về cả tên nhân viên lẫn phòng ban từ các ô C5:D14. Bạn không cần viết hai công thức cho hai cột.
- Ví dụ 3: thêm đối số if_not_found vào ví dụ 2, để thay #N/A bằng một thông báo dễ hiểu.
- Ví dụ 4: tìm thu nhập trong ô E2 ở cột C và trả về thuế suất ở cột B. if_not_found là 0, match_mode là 1 (lấy mục lớn hơn tiếp theo), search_mode là 1. Ở đây cột tìm kiếm nằm bên phải cột trả về.
- Ví dụ 5: lồng hai XLOOKUP để khớp cả chiều dọc lẫn chiều ngang: tìm Lợi nhuận Gộp ở cột B, tìm Qtr1 ở hàng C5:F5, rồi trả về giá trị tại giao điểm. Cách này giống kết hợp INDEX và MATCH.
- Ví dụ 6: dùng SUM với hai XLOOKUP để tính tổng mọi giá trị nằm giữa hai mục: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10)).
Ví dụ 4 là cái mình thấy đáng học nhất. Nó cho thấy XLOOKUP tra được sang bên trái, và kết hợp match_mode 1 để xử lý bảng bậc thuế, nơi thu nhập hiếm khi khớp đúng một mốc trong bảng. Microsoft cũng gợi ý dùng XLOOKUP để thay thế hàm HLOOKUP.
Lời khuyên của mình
Nếu bạn dùng Excel cho Microsoft 365, Excel 2021 hoặc Excel 2024, hãy dùng XLOOKUP cho mọi tra cứu mới và luôn điền if_not_found để bảng không đầy lỗi #N/A. Nếu tệp sẽ được mở trên Excel 2016 hoặc 2019, giữ VLOOKUP để tránh rắc rối. Nhớ rằng search_mode 2 và -2 cần dữ liệu đã sắp xếp. Nếu bạn dùng bảng tính để theo dõi lượt xem và thu nhập kênh, hai bài này có thể hữu ích: 1000 view YouTube được bao nhiêu tiền và cách bật kiếm tiền YouTube.
Câu hỏi thường gặp
Excel 2016 có hàm XLOOKUP không?
Không. XLOOKUP không sẵn dùng trong Excel 2016 và Excel 2019, dù bạn có thể mở một tệp có XLOOKUP do người khác tạo bằng Excel mới hơn.
XLOOKUP mặc định khớp chính xác hay gần đúng?
Khớp chính xác. match_mode mặc định là 0, không tìm thấy thì trả về #N/A.
XLOOKUP có tra cứu sang bên trái được không?
Có. XLOOKUP trả về kết quả từ cột khác bất kể cột trả về nằm ở bên nào, còn VLOOKUP chỉ xem từ trái sang phải.
Làm sao để XLOOKUP không hiện #N/A?
Điền đối số if_not_found. Khi không tìm thấy kết quả khớp hợp lệ, XLOOKUP trả về văn bản bạn cung cấp.
search_mode -1 dùng để làm gì?
Để tìm đảo ngược, bắt đầu từ mục cuối cùng của mảng tìm kiếm.
XLOOKUP có thay được HLOOKUP không?
Có. Microsoft nói bạn cũng có thể dùng XLOOKUP để thay thế hàm HLOOKUP.
