Hàm XLOOKUP trong Google Sheets: cú pháp, tra nhiều cột, tra sang trái và sửa lỗi #N/A

Bạn có một bảng gốc (danh sách khách hàng, bảng giá sản phẩm) và một chỗ khác chỉ có mã: ô bán hàng, phiếu thu, báo giá. XLOOKUP lấy mã, dò trong cột tìm, gặp dòng khớp thì trả về ô ở cột kết quả của dòng đó. Thông tin khách chỉ sửa ở một nơi, mọi chỗ khác tự lấy theo.

Có trong Google Sheets

Từ tháng 8/2022

Mặc định

Khớp chính xác, tìm từ trên xuống

Không tìm thấy

#N/A, hoặc chữ bạn ghi ở đối số thứ 4

Cột kết quả

Nằm bên trái hay bên phải đều được, lấy được nhiều cột

Các ví dụ dùng bảng khách hàng của một cửa hàng: cột A Mã KH, B Tên khách, C SĐT, D Hạng, E Điểm tích luỹ, dữ liệu ở dòng 2–6. Công thức viết theo khu vực Việt Nam nên đối số cách nhau bằng dấu chấm phẩy.

=XLOOKUP(khoá_tìm_kiếm; dải_ô_tìm_kiếm; dải_ô_kết_quả; [giá_trị_khi_không_có_kết_quả_trùng_khớp]; [chế_độ_khớp]; [chế_độ_tìm_kiếm])

=XLOOKUP(G2; $A$2:$A$6; $B$2:$B$6)

Thử lại: G2 = KH003. XLOOKUP dò cột A từ trên xuống: KH001, KH002, KH003 — khớp ở vị trí thứ 3. Ô thứ 3 của B2:B6 là B4 = Lê Thu Hà. Đổi dải kết quả thành $E$2:$E$6 thì ra 3.100 điểm. G2 = KH009 thì ra #N/A vì không có mã đó.

Khác với VLOOKUP, bạn không phải đếm "cột thứ mấy": cột kết quả được chỉ thẳng. Chèn thêm cột vào giữa bảng gốc, Google tự dời địa chỉ trong công thức và kết quả vẫn đúng.

Đây là việc XLOOKUP làm gọn nhất. Thay vì ba công thức cho ba ô, đưa cả ba cột B:D vào dải kết quả. Kết quả tràn sang các ô bên phải.

=IF(G2=""; ""; XLOOKUP(G2; $A$2:$A$6; $B$2:$D$6; "Không có mã này"))

Thử lại: G2 = KH003 → H2 Lê Thu Hà, I2 0987 654 321, J2 Kim cương. G2 = KH002 → Trần Văn Hùng, 0912 345 678, Bạc. G2 = KH009 → H2 hiện "Không có mã này". G2 trống → cả dòng trống. Không có IF bên ngoài thì mọi dòng chưa gõ mã đều hiện "Không có mã này", rất rối khi bạn kéo công thức sẵn cho 100 dòng.

Chỉ lấy vài cột không liền nhau. Muốn tên và hạng mà bỏ SĐT, chọn cột bằng CHOOSECOLS: số 1 và 3 là cột thứ nhất và thứ ba của B2:D6.

=XLOOKUP(G2; $A$2:$A$6; CHOOSECOLS($B$2:$D$6; 1; 3))

Cột kết quả không cần nằm bên phải cột tìm. Biết tên khách mà cần mã ở cột A bên trái:

=XLOOKUP("Trần Văn Hùng"; $B$2:$B$6; $A$2:$A$6; "Không có")

Thử lại: "Trần Văn Hùng" ở vị trí thứ 2 của B2:B6, ô thứ 2 của A2:A6 là KH002. Với VLOOKUP, việc này phải chuyển sang INDEX/MATCH.

Chọn cột theo tiêu đề. Khi người dùng muốn tự chọn thông tin cần xem (Hạng, Điểm, SĐT), đặt tên cột vào ô H1 rồi lồng hai XLOOKUP: cái bên trong tìm tiêu đề trên hàng 1 để lấy ra cả cột, cái bên ngoài tìm mã trong cột đó.

=XLOOKUP(G2; $A$2:$A$6; XLOOKUP(H1; $B$1:$E$1; $B$2:$E$6))

Thử lại: H1 = Hạng, G2 = KH002 → "Hạng" là cột thứ 3 của B1:E1, nên dải kết quả là D2:D6; KH002 ở vị trí 2 → Bạc. Đổi H1 thành Điểm → 640. Ô H1 nên là danh sách thả xuống lấy từ hàng tiêu đề để không gõ sai — xem cách tạo danh sách thả xuống trong Google Sheets.

Đối số thứ 4 thay cho việc bọc IFNA bên ngoài. Nó chỉ chạy khi không tìm thấy khoá; các lỗi khác (dải ô lệch kích thước, kết quả không có chỗ tràn) vẫn hiện ra để bạn biết công thức có vấn đề.

Cẩn thận với số 0: tổng tiền sẽ không báo lỗi khi mã sai, giống như SUMIFS ra 0 khi điều kiện sai (xem hàm SUMIFS và COUNTIFS). Với tra mã trong hoá đơn, một dòng chữ "Không có mã này" dễ thấy hơn nhiều.

Khi bảng gốc có mã trùng (một khách bị nhập hai lần), XLOOKUP lấy dòng đầu tiên tính từ trên xuống mà không báo gì. Kiểm tra và dọn trùng theo bài lọc dữ liệu trùng lặp trong Google Sheets.

Hạng thành viên đi theo điểm tích luỹ: từ 0 điểm Thường, từ 500 Bạc, từ 1.000 Vàng, từ 3.000 Kim cương. Đặt bảng mốc ở J2:K5 rồi dùng chế độ khớp -1: không có đúng mốc thì lấy mốc nhỏ hơn gần nhất.

=XLOOKUP(E2; $J$2:$J$5; $K$2:$K$5; "Chưa xếp"; -1)

Thử lại: KH002 có 640 điểm. Không có mốc 640; các mốc không vượt 640 là 0 và 500, gần nhất là 500 → Bạc. KH001 1.250 → mốc 1.000 → Vàng. KH003 3.100 → Kim cương. KH004 80 → mốc 0 → Thường. Một khách bị trừ điểm còn -20 thì không có mốc nào nhỏ hơn → "Chưa xếp". Kết quả khớp đúng cột Hạng trong bảng khách.

Chế độ 1 làm ngược lại: lấy mốc lớn hơn gần nhất. Ví dụ bảng phí giao theo cân nặng tối đa: đến 1 kg 20.000đ, đến 3 kg 30.000đ, đến 5 kg 45.000đ. Gói 2,4 kg: =XLOOKUP(2,4; $J$2:$J$4; $K$2:$K$4; "Quá 5 kg"; 1) ra 30.000 (mốc 3 kg); gói 6 kg ra "Quá 5 kg".

Với chế độ tìm mặc định (từ trên xuống), bảng mốc không bắt buộc phải sắp xếp, khác VLOOKUP TRUE. Dù vậy, hãy cứ xếp mốc tăng dần để người khác đọc là hiểu. Cách làm bảng bậc thang thay cho IF lồng nhiều tầng có ở bài hàm IF nhiều điều kiện.

Đặt chế độ khớp là 2 thì khoá tìm hiểu dấu sao (*) là chuỗi bất kỳ, dấu hỏi (?) là đúng một ký tự. Hữu ích khi chỉ nhớ một phần tên khách:

=XLOOKUP("*"&G2&"*"; $B$2:$B$6; $A$2:$A$6; "Không có"; 2)

Thử lại: G2 = Hùng → chỉ "Trần Văn Hùng" chứa chữ này → KH002. G2 = Lan → "Võ Ngọc Lan" → KH005. Tìm không phân biệt hoa thường nhưng phân biệt dấu: "hung" không khớp "Hùng". Nhiều tên cùng khớp thì chỉ ra tên đầu tiên; cần cả danh sách thì dùng FILTER hoặc QUERY (xem cách dùng hàm QUERY).

Không đặt chế độ 2 thì dấu * chỉ là một ký tự bình thường, nên =XLOOKUP("*Hùng*"; B2:B6; A2:A6) ra #N/A.

Bảng đơn hàng ghi theo thời gian, mỗi khách mua nhiều lần. Muốn lần mua gần nhất, đặt chế độ tìm kiếm là -1 (từ dưới lên). Bảng đơn: A Ngày, B Mã KH, C Số tiền.

=XLOOKUP("KH002"; $B$2:$B$6; $A$2:$C$6; "Chưa mua"; 0; -1)

Thử lại: tìm từ dòng 6 lên, KH002 gặp ở dòng 5 trước → 18/10/2026 · KH002 · 1.200.000. Bỏ hai đối số cuối thì tìm từ trên xuống, ra lần mua đầu tiên 02/10/2026 · 450.000. KH004 chưa có đơn → "Chưa mua". Ô kết quả ngày có thể hiện thành số kiểu 46313 — chỉ cần định dạng ô là ngày. Cách này chỉ đúng khi đơn mới luôn thêm xuống dưới; bảng bị sắp xếp lại thì nên lọc theo ngày lớn nhất.

#N/A nghĩa là không tìm thấy. Khi mã thật sự không có thì lỗi là đúng. Còn khi mã nằm ngay trong bảng mà vẫn #N/A, thường là một trong ba trường hợp sau.

1. Khoảng trắng thừa. "KH003 " (có dấu cách cuối, hay gặp khi dán từ Zalo, email) khác "KH003". Kiểm tra bằng =LEN(A4): ra 6 thay vì 5 là có ký tự thừa. Khoảng trắng nằm ở ô bạn gõ thì bọc TRIM(G2). Nằm trong bảng gốc thì chọn cột mã, vào Dữ liệu › Dọn dẹp dữ liệu › Cắt bớt khoảng trắng, hoặc cắt ngay trong công thức:

=XLOOKUP(TRIM(G2); ARRAYFORMULA(TRIM($A$2:$A$6)); $B$2:$B$6; "Không có mã này")

2. Số nằm dưới dạng chữ. Mã khách 1025 xuất từ phần mềm thường là chữ (nằm sát trái ô), còn ô bạn gõ 1025 là số (nằm sát phải). Trông giống nhau nhưng không khớp. Kiểm tra bằng =ISTEXT(A2). Đưa khoá tìm về cùng kiểu với cột mã: cột mã là chữ thì dùng G2&"", cột mã là số còn G2 là chữ thì dùng VALUE(G2). Cách nhận ra và sửa số dạng chữ hàng loạt có ở bài sửa lỗi SUM bằng 0 trong Google Sheets.

=XLOOKUP(G2&""; $A$2:$A$50; $B$2:$B$50; "Không có mã này")

3. Số điện thoại viết khác nhau. Tra khách theo SĐT là nhu cầu rất hay gặp, nhưng cùng một số có thể là 0987 654 321, 0987.654.321, +84 987 654 321 hay số 987654321 (Google bỏ số 0 đầu khi bạn gõ như một con số). Cách chắc chắn: bỏ mọi ký tự không phải chữ số rồi so 9 số cuối ở cả hai phía.

=XLOOKUP(RIGHT(REGEXREPLACE(G2&""; "\D"; ""); 9); ARRAYFORMULA(RIGHT(REGEXREPLACE($C$2:$C$6&""; "\D"; ""); 9)); $A$2:$A$6; "Không thấy SĐT")

Thử lại: G2 = 0987.654.321 → bỏ dấu chấm còn 0987654321 → 9 số cuối 987654321. Ô C4 = 0987 654 321 → bỏ khoảng trắng còn 0987654321 → 987654321. Hai bên khớp → KH003. Gõ +84 987 654 321 hay số 987654321 cũng ra KH003.

Chọn nhanh: file chỉ dùng trên Google Sheets hoặc Excel mới thì dùng XLOOKUP. File còn phải mở bằng Excel 2016, 2019 thì dùng INDEX/MATCH, vì ô XLOOKUP sẽ báo lỗi ở đó. Những khác biệt khác giữa hai bên có ở bài Google Sheets hay Excel.

Nếu bảng khách của bạn là danh sách khách tiềm năng cần gọi lại, bài quản lý khách hàng tiềm năng bằng Google Sheets chỉ cách dựng cột, trạng thái và lịch chăm sóc để XLOOKUP có dữ liệu sạch mà tra.

• Hàm SUMIFS và COUNTIFS trong Google SheetsCộng, đếm theo nhiều điều kiện, theo tháng.
• Hàm IF nhiều điều kiệnIF lồng, IFS, tra bảng bậc thang.
• Lọc dữ liệu trùng lặp trong Google SheetsDọn mã trùng trước khi tra cứu.
• Sửa lỗi SUM bằng 0 trong Google SheetsNhận ra và sửa số dạng chữ.