Hàm VLOOKUP trong Excel: cách dùng, 2 điều kiện, 2 sheet
Hàm VLOOKUP là gì, cách dò tìm chính xác và gần đúng, VLOOKUP 2 điều kiện bằng cột phụ, VLOOKUP giữa 2 sheet và cách sửa lỗi #N/A thường gặp.
VLOOKUP (Vertical Lookup) tìm một giá trị ở cột đầu tiên của bảng dữ liệu, rồi trả về giá trị nằm cùng dòng ở cột bạn chỉ định. Đây là hàm tra cứu phổ biến nhất trong văn phòng: tra giá theo mã hàng, tra tên theo mã nhân viên, tra mức phụ cấp theo chức vụ.
Cú pháp VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Đối số | Ý nghĩa |
|---|---|
lookup_value | Giá trị cần tìm, ví dụ mã hàng ở A2 |
table_array | Bảng tra; cột đầu tiên phải chứa giá trị cần tìm. Nên cố định bằng F4: $F$2:$H$100 |
col_index_num | Số thứ tự cột muốn lấy, đếm từ cột đầu của bảng tra (cột đầu = 1) |
range_lookup | FALSE hoặc 0: dò chính xác. TRUE, 1 hoặc bỏ trống: dò gần đúng |
=VLOOKUP(A2;$F$2:$H$100;3;0).Dò tìm chính xác: tra giá theo mã hàng
Bảng giá ở F2:H100 gồm Mã hàng, Tên hàng, Đơn giá. Ở bảng bán hàng, cột A là mã, cần lấy đơn giá:
=VLOOKUP(A2, $F$2:$H$100, 3, FALSE)Hầu hết trường hợp tra mã, tra tên đều phải dùng FALSE/0. Quên đối số cuối là lỗi rất phổ biến: Excel mặc định dò gần đúng và có thể trả về giá của mặt hàng khác mà không báo lỗi.
Dò tìm gần đúng: tra theo khoảng
Dò gần đúng dùng khi tra theo ngưỡng, ví dụ hoa hồng theo doanh số. Bảng tra K2:L5 có cột ngưỡng sắp xếp tăng dần:
| Doanh số từ | Hoa hồng |
|---|---|
| 0 | 0% |
| 50.000.000 | 2% |
| 100.000.000 | 3% |
| 200.000.000 | 5% |
=VLOOKUP(C2, $K$2:$L$5, 2, TRUE)Excel tìm ngưỡng lớn nhất nhưng không vượt quá C2. Doanh số 130 triệu sẽ nhận 3%. Nếu cột ngưỡng không tăng dần, kết quả sai.
VLOOKUP giữa 2 sheet
Bảng tra nằm ở sheet khác, ví dụ sheet BangGia:
=VLOOKUP(A2, BangGia!$A$2:$C$500, 3, 0)Cách nhanh nhất: gõ =VLOOKUP(A2, rồi bấm sang sheet kia, kéo chọn vùng, nhấn F4, gõ tiếp. Nếu tên sheet có khoảng trắng, Excel tự bọc trong nháy đơn: 'Bảng giá'!$A$2:$C$500. Tra sang file khác cũng làm tương tự, nhưng file nguồn nên mở cùng lúc, đổi tên hoặc di chuyển file nguồn sẽ làm hỏng liên kết.
VLOOKUP 2 điều kiện bằng cột phụ
VLOOKUP chỉ tìm theo một giá trị. Khi cần tìm theo hai điều kiện (ví dụ Mã nhân viên và Tháng), tạo cột phụ ghép hai điều kiện và đặt nó làm cột đầu tiên của bảng tra:
- Chèn cột trống bên trái bảng dữ liệu (cột A), gõ
=B2&"|"&C2(mã & tháng) rồi kéo xuống. - Ở bảng cần tra, ghép điều kiện y hệt:
=VLOOKUP(G2&"|"&H2, $A$2:$E$500, 5, 0).
Ký tự | giúp tránh trùng nhầm kiểu “NV1”&“12” với “NV11”&“2”. Trên Microsoft 365, có thể bỏ cột phụ bằng XLOOKUP hoặc FILTER, xem so sánh trong bài VLOOKUP, XLOOKUP hay INDEX/MATCH.
Sửa lỗi #N/A và kết quả sai
| Triệu chứng | Nguyên nhân thường gặp | Cách xử lý |
|---|---|---|
#N/A dù nhìn thấy mã có trong bảng | Khoảng trắng thừa, số lưu dạng văn bản | Dùng TRIM; đổi kiểu dữ liệu cho đồng nhất (xem làm sạch dữ liệu) |
#N/A khi kéo công thức xuống | Quên $ nên vùng tra bị trượt | Cố định vùng bằng F4 |
#REF! | col_index_num lớn hơn số cột của vùng | Mở rộng vùng hoặc sửa số cột |
| Kết quả sai, không báo lỗi | Bỏ trống đối số cuối | Thêm 0 |
| Không tra được cột bên trái | VLOOKUP chỉ nhìn sang phải | Dùng INDEX/MATCH hoặc XLOOKUP |
Muốn hiện ô trống hoặc “Không có” thay vì #N/A, bọc thêm IFERROR hoặc IFNA.
Trên Google Sheets
Google Sheets có VLOOKUP cú pháp giống Excel, kể cả tham chiếu sheet khác. Muốn tra từ file Sheets khác, kết hợp với IMPORTRANGE. Google Sheets cũng có XLOOKUP nếu bạn muốn tra hai chiều.
col_index_num lệch. Định dạng bảng tra thành Excel Table hoặc chuyển sang XLOOKUP để tránh lỗi này.