Cần tìm đơn giá theo mã sản phẩm, điền phụ cấp theo chức vụ hay xếp loại theo điểm cho cả trăm dòng dữ liệu? Đó là lúc hàm VLOOKUP trong Excel phát huy tác dụng. Bài này đi qua công thức, các ví dụ thường gặp, những lỗi hay mắc và cả hạn chế của hàm.
Hàm VLOOKUP trong Excel là gì?
VLOOKUP là viết tắt của Vertical Lookup, nghĩa là dò tìm theo chiều dọc. Hàm tìm giá trị theo cột đầu tiên của vùng dữ liệu, từ trên xuống dưới, rồi trả về giá trị tương ứng trên cùng hàng. Nếu cần dò theo hàng ngang thì dùng hàm HLOOKUP.
Với Excel trong Microsoft 365 và Excel 2021 trở lên, Microsoft khuyến nghị dùng hàm XLOOKUP, phiên bản cải tiến có thể dò tìm theo mọi hướng và mặc định tìm chính xác.
Công thức hàm VLOOKUP
=VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup])
- Lookup_value: giá trị cần tìm, nhập trực tiếp hoặc tham chiếu tới một ô.
- Table_array: vùng dữ liệu để dò tìm. Giá trị cần tìm phải nằm ở cột đầu tiên của vùng này.
- Col_index_num: số thứ tự của cột cần lấy dữ liệu, tính từ cột đầu tiên của Table_array (cột đầu là 1).
- Range_lookup: kiểu dò tìm. 0 (FALSE) là tìm chính xác. 1 (TRUE) hoặc bỏ trống là tìm gần đúng.
Khi sao chép công thức xuống các ô khác, hãy cố định Table_array bằng dấu $ (ví dụ $H$6:$J$13) hoặc nhấn F4 sau khi chọn vùng.
5 ví dụ hàm VLOOKUP
Ví dụ 1: Tính phụ cấp theo chức vụ
Công ty có mức phụ cấp cho từng chức vụ ở bảng 2 (B16:C21). Cần điền phụ cấp cho từng nhân viên ở bảng 1 dựa vào chức vụ.
Tại ô E4, nhập công thức =VLOOKUP(D4,$B$16:$C$21,2,0):
- D4 là chức vụ cần tìm.
- $B$16:$C$21 là bảng phụ cấp, được cố định bằng dấu $.
- 2 là cột thứ hai (cột phụ cấp) của bảng.
- 0 để tìm chính xác.
Kéo nút điền (ô vuông nhỏ ở góc dưới bên phải ô E4) xuống để sao chép công thức cho các nhân viên còn lại.
Ví dụ 2: Xếp loại học sinh theo điểm
Bảng 1 là điểm của học sinh, bảng 2 (B11:C15) là mốc điểm và xếp loại tương ứng, sắp xếp tăng dần.
Tại ô E4, nhập =VLOOKUP(D4,$B$11:$C$15,2,1). Với kiểu dò tìm gần đúng (1), Excel tìm giá trị lớn nhất nhỏ hơn hoặc bằng điểm cần xếp loại trong cột mốc điểm. Ví dụ điểm 9,1 sẽ khớp với mốc 8,5 và trả về Giỏi.
Kéo nút điền xuống để xếp loại cho các học sinh còn lại.
Ví dụ 3: Dò tìm gần đúng theo khoảng điểm
Xếp loại theo điểm trung bình với quy định: từ 9 trở lên là Giỏi, từ 6,5 đến dưới 9 là Khá, từ 5 đến dưới 6,5 là Trung bình. Tại ô D4, nhập =VLOOKUP(C4,$B$10:$C$12,2,TRUE).
Lưu ý: khi dò tìm gần đúng, cột đầu tiên của bảng tra cứu phải được sắp xếp tăng dần, nếu không kết quả sẽ sai.
Ví dụ 4: Kết hợp VLOOKUP với Data Validation
Tạo ô chọn số báo danh dạng danh sách thả xuống, rồi dùng VLOOKUP để tự hiện tên và điểm. Chọn ô C10, vào thẻ Data > Data Validation.
Trong thẻ Settings, mục Allow chọn List, mục Source chọn vùng B4:B7.
Nhập công thức =VLOOKUP(C10,B4:D7,2,0) để lấy tên và =VLOOKUP(C10,B4:D7,3,0) để lấy điểm. Khi đổi số báo danh ở ô C10, tên và điểm tự cập nhật.
Ví dụ 5: Kết hợp VLOOKUP với hàm LEFT tìm ngành học
Ký tự đầu của số báo danh là mã ngành. Dùng =VLOOKUP(LEFT(C10),F4:G5,2,0): hàm LEFT lấy ký tự đầu tiên của ô C10, VLOOKUP dùng ký tự đó để tra tên ngành trong bảng mã ngành.
Các lỗi thường gặp khi dùng hàm VLOOKUP
Lỗi #N/A
VLOOKUP chỉ tìm ở cột đầu tiên của Table_array. Ví dụ Table_array là A2:C10 thì hàm tìm ở cột A, nếu tên nông sản nằm ở cột B sẽ báo #N/A. Sửa bằng cách đổi Table_array thành B2:C10, hoặc dùng INDEX kết hợp MATCH.
Lỗi #N/A cũng xuất hiện khi giá trị cần tìm không có trong bảng, như “Rau muống” ở ví dụ dưới. Có thể bọc công thức bằng hàm IFERROR để hiện nội dung khác thay cho #N/A. Nếu chắc chắn dữ liệu có trong bảng mà vẫn báo lỗi, hãy kiểm tra khoảng trắng thừa hoặc ký tự ẩn (có thể dùng hàm TRIM, CLEAN).
Lỗi #REF!
Col_index_num lớn hơn số cột của Table_array. Ví dụ Col_index_num là 3 nhưng B2:C10 chỉ có 2 cột.
Lỗi #VALUE!
Col_index_num nhỏ hơn 1, ví dụ nhập 0. Cột đầu tiên của Table_array luôn là cột 1.
Lỗi #NAME?
Giá trị cần tìm dạng chữ nhưng không đặt trong dấu ngoặc kép. Sửa Cải xoăn thành “Cải xoăn”.
Lưu ý khi dùng hàm VLOOKUP
Dùng tham chiếu tuyệt đối cho bảng dò tìm
Đặt dấu $ trước hàng, cột của Table_array để vùng dò tìm không bị dịch chuyển khi sao chép công thức. Ví dụ =VLOOKUP(B13,$B$2:$C$10,2,0) khi chép xuống ô dưới vẫn giữ nguyên vùng B2:C10.
Nếu không cố định, công thức =VLOOKUP(B13,B2:C10,2,0) khi chép xuống sẽ thành =VLOOKUP(B14,B3:C11,2,0) và có thể cho kết quả sai.
Không lưu số dưới dạng văn bản
Nếu cột dò tìm chứa số ở dạng văn bản còn giá trị cần tìm là số (hoặc ngược lại), VLOOKUP sẽ báo #N/A.
Chuyển vùng dữ liệu về cùng định dạng Number trong thẻ Home là hàm hoạt động bình thường.
Bảng dò tìm có giá trị trùng
VLOOKUP chỉ trả về kết quả đầu tiên tìm thấy từ trên xuống. Ví dụ “Táo” có hai giá trị 97 và 23, hàm trả về 97.
Để loại bỏ giá trị trùng, bôi đen bảng và chọn Data > Remove Duplicates.
Hoặc dùng Pivot Table để tổng hợp các giá trị theo từng mục.
Hạn chế của hàm VLOOKUP
Chỉ dò tìm từ trái sang phải
Cột cần lấy dữ liệu phải nằm bên phải cột dò tìm. Muốn tìm từ phải sang trái, hãy dùng INDEX kết hợp MATCH hoặc XLOOKUP.
Chỉ trả về giá trị đầu tiên
Khi có nhiều dòng trùng giá trị dò tìm, VLOOKUP bỏ qua các dòng sau.
Số thứ tự cột được nhập cố định
Khi chèn hoặc xóa cột trong bảng, Col_index_num không tự cập nhật. Có thể dùng MATCH để tự tính số thứ tự cột theo tiêu đề.
Mặc định là dò tìm gần đúng
Nếu bỏ trống Range_lookup, hàm dò tìm gần đúng và có thể trả kết quả sai với dữ liệu chưa sắp xếp. Hãy luôn nhập 0 (FALSE) khi cần tìm chính xác.
Có thể làm chậm bảng tính lớn
Bảng tính có rất nhiều công thức VLOOKUP trên vùng dữ liệu lớn sẽ tính toán chậm hơn. Hãy giới hạn vùng dò tìm vừa đủ thay vì chọn cả cột.
Khi đã quen với VLOOKUP, bạn nên tìm hiểu thêm INDEX kết hợp MATCH hoặc XLOOKUP để xử lý những trường hợp VLOOKUP chưa làm được. Xem thêm các thủ thuật khác trong chuyên mục Office.
Để lại một bình luận