VLOOKUP nâng cao là cách kết hợp VLOOKUP với cột phụ, MATCH và hàm xử lý lỗi để giải những bài tra cứu phức tạp hơn. Bạn có thể dò theo mã hàng và chi nhánh, chọn cột trả về theo tiêu đề, đồng thời phát hiện dữ liệu thiếu trước khi đưa kết quả lên báo cáo.
Bài này dùng một bảng giá cửa hàng giả lập, có địa chỉ ô cụ thể để bạn tự nhập và đối chiếu. Các công thức dùng dấu phẩy; nếu Excel của bạn yêu cầu dấu chấm phẩy, hãy đổi dấu phân cách đối số tương ứng.
Chuẩn bị bảng giá và xác định khóa tra cứu
Tạo sheet tên BangGia, nhập tiêu đề ở dòng 1 và dữ liệu từ dòng 2 đến dòng 5 như bảng dưới. Đơn giá là số nguyên, đơn vị đồng; đừng nhập kèm chữ “đ” vào ô số.
| Dòng | A: Khóa | B: Mã hàng | C: Chi nhánh | D: Giá lẻ | E: Giá sỉ |
|---|---|---|---|---|---|
| 2 | CF01|HN | CF01 | HN | 65000 | 58000 |
| 3 | CF01|HCM | CF01 | HCM | 68000 | 60000 |
| 4 | TE02|HN | TE02 | HN | 45000 | 39000 |
| 5 | TE02|HCM | TE02 | HCM | 47000 | 41000 |
Ở sheet TraCuu, nhập CF01 vào A2, HCM vào B2 và Giá sỉ vào C1. Kết quả cần tìm là 60000. Nếu chỉ dò mã CF01, bạn chưa biết cần giá ở Hà Nội hay TP.HCM: bài toán thiếu một điều kiện.
Trước khi viết hàm, hãy chốt ý nghĩa mỗi dòng: ở đây một dòng là giá của một mã hàng tại một chi nhánh. Nếu giá còn thay đổi theo ngày hiệu lực, khóa hai thành phần chưa đủ; cần thiết kế lại bảng trước khi tính.
1. Dò hai điều kiện bằng cột khóa ghép
Trong BangGia!A2, nhập công thức dưới rồi kéo đến A5. Các giá trị cột A trong bảng trên chính là kết quả bạn cần thu được.
=B2&"|"&C2Ở TraCuu!D2, dò giá sỉ bằng khóa tương ứng:
=VLOOKUP(A2&"|"&B2,BangGia!$A$2:$E$5,5,FALSE)Cột A là cột thứ nhất của vùng tra cứu; vì vậy Giá sỉ ở cột E có chỉ số 5. Vùng nguồn được khóa bằng dấu $ để giữ nguyên khi sao chép. A2 và B2 vẫn dịch theo từng dòng đơn hàng.
Dấu phân cách tránh trường hợp AB ghép với C và A ghép với BC đều thành ABC. Hãy chọn ký tự không xuất hiện trong hai trường nguồn. Nếu dữ liệu chứa dấu |, cần quy tắc mã hóa khác hoặc chuyển sang phép nối theo nhiều cột.
Với mã hàng, nên ghi rõ FALSE để dò chính xác. Microsoft mô tả bốn đối số trong tài liệu hàm VLOOKUP. Nếu bạn cần cách nối hai bảng mà không tạo khóa phụ, xem thêm tra cứu bằng Merge trong Power Query.
2. Chọn cột trả về bằng VLOOKUP kết hợp MATCH
Khi người xem muốn chuyển giữa Giá lẻ và Giá sỉ, sửa số 4 thành 5 trong từng công thức rất dễ sót. Bạn có thể dùng tiêu đề ở C1 để xác định vị trí cột:
=VLOOKUP(A2&"|"&B2,BangGia!$A$2:$E$5,MATCH($C$1,BangGia!$A$1:$E$1,0),FALSE)Với C1 là Giá sỉ, MATCH trả về 5; đổi C1 thành Giá lẻ, MATCH trả về 4. Công thức lần lượt cho 60000 và 68000. MATCH dò đúng tiêu đề nhờ đối số 0, còn dấu $ giữ ô lựa chọn C1 cố định.
Hai vùng phải bắt đầu ở cùng cột và bao quát cùng các cột. Nếu VLOOKUP bắt đầu tại A nhưng MATCH chỉ xét từ B, chỉ số trả về sẽ lệch một cột. Tiêu đề cũng phải duy nhất và thống nhất khoảng trắng.
Hãy thử chèn một cột trước Giá lẻ rồi kiểm tra lại phạm vi công thức. MATCH giúp giảm việc đếm cột bằng tay, nhưng bạn vẫn phải bảo đảm cả vùng tiêu đề và vùng dữ liệu đã bao gồm cột mới. Microsoft có hướng dẫn nền tảng về kết hợp các hàm tra cứu.
3. Xử lý thiếu mã mà vẫn nhìn thấy lỗi cấu trúc
Đổi A2 sang XX99 sẽ làm phép dò không tìm thấy khóa. Trong bảng đối chiếu, thông báo “Chưa có giá” dễ theo dõi hơn một ô lỗi:
=IFNA(VLOOKUP(A2&"|"&B2,BangGia!$A$2:$E$5,5,FALSE),"Chưa có giá")Theo tài liệu IFNA, hàm chỉ thay thế lỗi #N/A. IFERROR bắt nhiều loại lỗi hơn, bao gồm #REF!, nên có thể che mất việc tham chiếu sai. Khi đang xây file, hãy kiểm tra công thức gốc trước rồi mới thêm phần hiển thị thông báo.
Ví dụ trên cố ý dùng chỉ số cột 5. Nếu bạn bọc cả công thức có MATCH bằng IFNA, tiêu đề C1 viết sai cũng có thể tạo #N/A và bị đổi thành “Chưa có giá”. Cần kiểm tra danh sách tiêu đề riêng để tránh hiểu nhầm nguyên nhân.
Không tự thay giá thiếu bằng 0: nhân số lượng với 0 sẽ tạo doanh thu bằng 0 dù thực tế chưa có đơn giá. Nên giữ cột trạng thái riêng, xử lý các dòng thiếu rồi mới tổng hợp.
4. Kiểm tra khóa trùng và kiểu dữ liệu
Ở một cột kiểm tra của sheet TraCuu, đếm số lần xuất hiện của khóa:
=COUNTIF(BangGia!$A$2:$A$5,A2&"|"&B2)Với bộ mã minh họa không chứa ký tự đại diện, kết quả 0 nghĩa là thiếu khóa, 1 là có đúng một dòng, lớn hơn 1 là trùng. Nếu mã thực tế chứa * hoặc ?, cần xử lý ký tự đại diện trước khi dùng COUNTIF.
Thử thêm một dòng CF01|HCM có giá sỉ khác và mở rộng phạm vi đến dòng 6. VLOOKUP dò chính xác vẫn lấy kết quả khớp đầu tiên, nên ô có số chưa chứng minh bảng giá đúng. Tài liệu VLookup trên Microsoft Learn nêu rõ hành vi khi có nhiều giá trị khớp, cùng các lưu ý về dữ liệu văn bản và số.
Với mã 0012, hãy thống nhất kiểu văn bản để giữ số 0 đầu. Nếu một nguồn đã biến mã thành 12, đổi định dạng hiển thị chưa chắc khôi phục đúng danh tính mã. Khoảng trắng ở mã hoặc chi nhánh cũng cần làm sạch ở nguồn, không sửa lẻ từng ô kết quả.
5. Bộ kiểm tra trước khi bàn giao báo cáo
Chạy lần lượt các ca dưới đây trên bản sao của file. Ghi kết quả kỳ vọng trước khi sửa dữ liệu để tránh chỉ nhìn xem công thức có trả số hay không.
- CF01 và HCM: giá lẻ 68000, giá sỉ 60000, số khóa khớp bằng 1.
- TE02 và HN: giá lẻ 45000, giá sỉ 39000.
- XX99 và HCM: thông báo thiếu giá, số khóa khớp bằng 0.
- Thêm khóa trùng: cột kiểm tra phải cảnh báo dù VLOOKUP vẫn trả một giá.
- Thêm khoảng trắng vào mã hoặc nhập sai tiêu đề: phải truy được nguyên nhân trước khi tổng hợp.
Khi đưa dữ liệu vào dashboard bằng Excel, nên theo dõi riêng số dòng chưa có giá. Kiểm tra đó giúp người nhận biết báo cáo đã đủ dữ liệu hay vẫn cần bổ sung.
Câu hỏi thường gặp
VLOOKUP nâng cao có phải một hàm riêng không?
Không. Đây là cách dùng VLOOKUP cùng cột phụ hoặc hàm khác để giải bài toán cụ thể. Trong ví dụ này, khóa ghép bổ sung điều kiện, MATCH chọn cột và IFNA xử lý trường hợp không tìm thấy.
Có cần sắp xếp bảng trước khi dò chính xác không?
Không cần với FALSE. Chế độ TRUE dùng cho bài toán gần đúng và yêu cầu cột dò được sắp xếp phù hợp; đừng dùng chế độ đó để thay thế việc kiểm tra mã hàng thiếu.
VLOOKUP có trả về toàn bộ dòng trùng không?
Không với cách dò chính xác trong bài: hàm trả về giá trị của dòng khớp đầu tiên. Nếu cần toàn bộ giao dịch của một mã, hãy chọn công cụ lọc hoặc tổng hợp phù hợp thay vì coi bảng giao dịch là danh mục duy nhất.
Khi nào nên chuyển việc tra cứu sang Power Query?
Khi mỗi kỳ bạn phải nhập lại nhiều file, làm sạch mã và ghép danh mục lặp đi lặp lại, nên cân nhắc Power Query. Trước khi nối bảng, vẫn phải xác định khóa và kiểm tra dữ liệu trùng để tránh làm tăng số dòng ngoài ý muốn.
Nếu bạn cần người xây báo cáo Power BI từ các file bán hàng và danh mục giá, hãy chuẩn bị dữ liệu mẫu cùng quy tắc tra cứu rồi xem cách gửi yêu cầu và báo giá làm Power BI. Phạm vi rõ ràng giúp người làm hiểu đúng kết quả bạn cần bàn giao.
Sẵn sàng xây dựng dashboard Power BI?
Kết nối với freelancer Power BI phù hợp, xem báo cáo nhúng ngay trên trình duyệt và tải file .pbix về sau khi thanh toán.
Đào Ngọc Tú
Cộng tác viên chuyên gia tại SenData. Chia sẻ kiến thức về phát triển Power BI, trực quan hóa dữ liệu và best practice business intelligence.
