XLOOKUP là gì? Cách dùng XLOOKUP thay VLOOKUP trong Excel
Mục lục (12 phần)
Bạn có một bảng đơn hàng chỉ ghi mã sản phẩm, còn đơn giá nằm ở bảng khác. Muốn tính tiền, bạn phải lấy đơn giá về theo đúng mã. Đó là việc của hàm XLOOKUP. Bài này giải thích XLOOKUP là gì, cách viết từng bước, và khi nào vẫn phải dùng cách khác.
XLOOKUP là gì?
XLOOKUP là hàm tra cứu của Excel: tìm một giá trị trong một cột (hoặc hàng), rồi trả về giá trị tương ứng ở cột (hoặc hàng) khác. Bạn chỉ cần trả lời ba câu: tìm cái gì, tìm ở đâu, lấy về từ đâu.
=XLOOKUP(giá trị tìm, vùng tìm, vùng lấy về, [khi không thấy], [kiểu khớp], [kiểu tìm])
Ba đối số đầu là bắt buộc. Đối số thứ tư rất nên dùng, hai đối số cuối chỉ cần trong một số trường hợp.
Cách dùng XLOOKUP từng bước
Giả sử trang Sản phẩm có mã ở cột A từ dòng 5 đến 18, đơn giá ở cột E. Trang Đơn hàng có mã ở ô B5.
- Bấm vào ô cần điền đơn giá, gõ
=XLOOKUP(. - Bấm vào ô B5, gõ dấu phẩy.
- Sang trang Sản phẩm, chọn A5:A18, bấm F4 để khoá vùng, gõ dấu phẩy.
- Chọn E5:E18, bấm F4.
- Gõ dấu phẩy, rồi gõ 0 để trả về 0 khi không tìm thấy, đóng ngoặc, Enter.
- Kéo công thức xuống cả cột.
=XLOOKUP(B5,'Sản phẩm'!$A$5:$A$18,'Sản phẩm'!$E$5:$E$18,0)
Máy cài vùng Việt Nam dùng dấu chấm phẩy thay cho dấu phẩy giữa các đối số.
XLOOKUP khác VLOOKUP thế nào?
Ba khác biệt quan trọng nhất trong công việc hằng ngày:
- Tra sang trái.VLOOKUP chỉ lấy cột nằm bên phải cột tìm. XLOOKUP lấy được cột ở bất kỳ phía nào.
- Chèn cột không làm hỏng công thức.VLOOKUP dựa vào số thứ tự cột, chèn thêm một cột là ra số sai. XLOOKUP dùng vùng cụ thể.
- Mặc định an toàn hơn.VLOOKUP quên đối số cuối sẽ tìm gần đúng và có thể trả về số sai mà không báo gì. XLOOKUP mặc định khớp chính xác.
Báo rõ khi không tìm thấy
Không có đối số thứ tư, mã không tồn tại sẽ hiện lỗi #N/A. Đối số thứ tư cho bạn chọn cách báo:
| Cột | Đối số thứ tư nên dùng | Vì sao |
|---|---|---|
| Tên hàng | "Không tìm thấy" | Người đọc biết phải sửa mã |
| Đơn giá | 0 | Cột thành tiền vẫn tính được, không lan lỗi |
Tra theo bậc: tính hoa hồng
Nhiều việc không cần khớp chính xác mà cần tìm bậc: doanh số 75 triệu đồng thuộc bậc từ 50 triệu. Đặt đối số thứ năm là -1:
=XLOOKUP(D5,$A$5:$A$9,$B$5:$B$9,,-1)
Hai dấu phẩy liền nhau nghĩa là bỏ qua đối số thứ tư. Với -1, Excel lấy giá trị bằng, nếu không có thì lấy giá trị nhỏ hơn gần nhất.
Vì sao XLOOKUP báo #N/A dù có mã?
Đây là lỗi hay gặp nhất, và thường không liên quan đến công thức:
- Dư dấu cách.Mã "VPP02 " có dấu cách cuối khác với "VPP02". Dùng TRIM cho sạch.
- Khác kiểu dữ liệu.Một bên là số 123, bên kia là chữ "123". Đưa về cùng kiểu.
- Vùng không khoá.Quên dấu $, kéo xuống làm vùng trượt và bỏ sót mã ở đầu bảng.
Máy không có XLOOKUP thì sao?
XLOOKUP có trên Microsoft 365, Excel 2021 trở lên và Excel trên web. Excel 2019 trở về trước báo #NAME?. Khi đó dùng INDEX MATCH:
=INDEX('Sản phẩm'!$E$5:$E$18,MATCH(B5,'Sản phẩm'!$A$5:$A$18,0))
MATCH tìm vị trí, INDEX lấy giá trị ở vị trí đó. Số 0 nghĩa là khớp chính xác. Kết hợp này chạy trên mọi phiên bản, nên vẫn nên biết nếu tệp của bạn gửi cho nhiều người.
Ví dụ đầy đủ: ghép đơn hàng với danh mục
Hãy xem một ví dụ giả định với ba mã hàng và bốn đơn:
| Mã đơn | Mã SP | Số lượng | Đơn giá tra được | Thành tiền |
|---|---|---|---|---|
| DH01 | VPP02 | 40 | 76.000 | 3.040.000 |
| DH02 | VPP04 | 12 | 42.000 | 504.000 |
| DH03 | VPP99 | 5 | 0 | 0 |
| DH04 | VPP02 | 10 | 76.000 | 760.000 |
Đơn DH03 có mã không tồn tại trong danh mục. Nhờ đối số thứ tư trả về 0, cột thành tiền vẫn tính được, còn ô tên hàng ở bên cạnh hiện Không tìm thấy để người nhập biết cần sửa. Nếu không xử lý, lỗi #N/A sẽ lan sang cột tổng và làm hỏng cả báo cáo.
Tìm giá trị gần nhất từ dưới lên
Đối số thứ sáu quyết định chiều tìm. Mặc định, Excel tìm từ trên xuống và dừng ở dòng đầu tiên khớp. Đặt -1 để tìm từ dưới lên. Cách này có ích khi bảng ghi theo thứ tự thời gian và bạn cần dòng mới nhất, ví dụ đơn gần đây nhất của một khách:
=XLOOKUP(A2,KhachHang,NgayDon,"Chưa có đơn",0,-1)
Trong công thức trên, KhachHang và NgayDon là hai vùng dữ liệu của bạn. Số 0 là khớp chính xác, số -1 là tìm từ dưới lên. Nếu dùng INDEX MATCH, việc này phức tạp hơn nhiều, vì MATCH khớp chính xác luôn dừng ở dòng đầu tiên.
Năm việc nên kiểm sau khi viết XLOOKUP
- Vùng tìm và vùng lấy về có cùng số dòng chưa?
- Đã khoá cả hai vùng bằng dấu đô la chưa?
- Thử gõ một mã sai xem ô báo có dễ hiểu không.
- Đếm số ô báo Không tìm thấy bằng COUNTIF để biết có bao nhiêu mã cần sửa.
- Cộng tổng cột vừa tra, đối chiếu với một con số bạn đã biết.
Bước thứ tư rất hữu ích với bảng dài. Thay vì lướt mắt qua hàng trăm dòng, bạn viết =COUNTIF(E5:E200,"Không tìm thấy") và biết ngay có cần xử lý gì không.
Khi nào không nên dùng XLOOKUP?
XLOOKUP chỉ trả về kết quả đầu tiên khớp. Nếu một mã có nhiều dòng và bạn cần tổng của tất cả các dòng đó, hãy dùng SUMIFS. Nếu cần đếm thì dùng COUNTIFS. Nhiều người dùng tra cứu cho việc cộng dồn, rồi thắc mắc vì sao số quá nhỏ: hàm chỉ lấy đúng dòng đầu tiên mà thôi.
Tóm lại, câu hỏi dạng tìm một giá trị theo mã thì dùng tra cứu. Câu hỏi dạng cộng, đếm, tính trung bình theo điều kiện thì dùng nhóm SUMIFS và COUNTIFS. Phân biệt được hai loại câu hỏi này, bạn sẽ chọn đúng hàm ngay từ đầu và đỡ mất công sửa về sau.
Câu hỏi thường gặp
XLOOKUP có phân biệt chữ hoa, chữ thường không?
Không. "vpp02" và "VPP02" được coi là giống nhau.
Có cần sắp xếp bảng trước khi dùng XLOOKUP không?
Không cần, kể cả khi tra theo bậc với đối số -1. Tuy nhiên, bảng bậc sắp tăng dần sẽ dễ đọc và dễ kiểm hơn.
XLOOKUP có thay được INDEX MATCH hoàn toàn không?
Trên Excel đời mới thì hầu hết các trường hợp là được. INDEX MATCH vẫn cần khi tệp phải chạy trên Excel cũ. Tra hai chiều có thể dùng XLOOKUP lồng hoặc INDEX với hai MATCH.
Muốn luyện XLOOKUP trên tệp có chấm từng ô cùng các hàm khác, bạn xem khoá Excel văn phòng từ cơ bản đến thành thạo.
Công ty và số liệu trong bài là giả định để minh hoạ.
