Hàm VLOOKUP trong Excel là công cụ tra cứu quen thuộc nhất với người làm bảng tính. Nếu từng phải dò từng dòng để tìm điểm của một học sinh hay giá của một mặt hàng, bạn sẽ thấy hàm này tiết kiệm thời gian đến mức nào. Chúng tôi dùng nó gần như mỗi tuần khi chấm sổ điểm và đối chiếu danh sách, nên bài này đi thẳng vào cách làm.
Hàm VLOOKUP là gì và dùng khi nào
VLOOKUP là viết tắt của “vertical lookup”, nghĩa là tra cứu theo chiều dọc. Hàm nhận một giá trị cần tìm, dò giá trị đó ở cột đầu tiên của một bảng, rồi trả về dữ liệu nằm cùng dòng ở một cột bạn chỉ định.
Hãy hình dung một danh bạ điện thoại. Bạn biết tên người cần gọi, và muốn lấy ra số điện thoại ở cột bên cạnh. VLOOKUP làm đúng việc đó, chỉ khác là nó chạy trên bảng tính.
Bạn nên dùng VLOOKUP trong những tình huống như sau:
- Lấy tên sản phẩm hoặc đơn giá từ bảng giá dựa trên mã hàng.
- Điền điểm thi vào bảng tổng hợp dựa trên mã học sinh.
- Ghép hai danh sách có chung một cột mã, chẳng hạn danh sách lớp và danh sách học phí nội bộ của một câu lạc bộ.
Hàm này hợp với bảng dữ liệu có cột khoá nằm ở bên trái. Nếu cột khoá nằm bên phải cột cần lấy, VLOOKUP sẽ gặp khó, và ta sẽ quay lại điểm này ở phần cuối bài.
Cú pháp và ý nghĩa từng tham số
Cú pháp đầy đủ của hàm như sau: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Có bốn tham số, và mỗi tham số đều có thể gây lỗi nếu hiểu sai. Microsoft cũng có trang mô tả hàm VLOOKUP để bạn đối chiếu.
Tham số thứ nhất và thứ hai
lookup_value là giá trị bạn muốn tìm. Đó có thể là một ô, ví dụ A2, hoặc một chuỗi nằm trong dấu nháy kép. Giá trị này phải tồn tại ở cột đầu tiên của vùng tra cứu.
table_array là vùng dữ liệu chứa cả cột khoá lẫn cột cần lấy. Bạn nên khoá vùng này bằng phím F4 để thành dạng $A$2:$D$50. Nếu không khoá, khi kéo công thức xuống, vùng tra cứu sẽ trượt theo và kết quả sai dần.
Tham số thứ ba và thứ tư
col_index_num là số thứ tự của cột cần lấy, tính từ cột đầu tiên của vùng. Cột đầu tiên là 1, cột kế tiếp là 2. Nhiều bạn đếm theo cột của cả trang tính nên lấy nhầm cột. Hãy luôn đếm trong phạm vi vùng bạn đã chọn.
range_lookup quyết định cách so khớp. Nhập FALSE (hoặc số 0) để tìm khớp chính xác. Nhập TRUE (hoặc số 1) để tìm khớp gần đúng, khi đó cột khoá bắt buộc phải sắp xếp tăng dần. Với dữ liệu mã học sinh hay mã hàng, chúng tôi gần như luôn dùng FALSE.
Ví dụ tra cứu điểm học sinh
Giả sử bạn có một bảng điểm ở sheet “Diem” với các cột: Mã học sinh (A), Họ tên (B), Điểm Toán (C), Điểm Văn (D). Dữ liệu nằm từ dòng 2 đến dòng 41. Ở sheet “TongHop”, bạn chỉ có cột mã học sinh và muốn điền điểm Toán tương ứng.
Các bước làm như sau:
- Chọn ô B2 ở sheet “TongHop”, nơi cần hiện điểm Toán.
- Gõ công thức =VLOOKUP(A2, Diem!$A$2:$D$41, 3, FALSE) rồi nhấn Enter.
- Số 3 nghĩa là cột thứ ba trong vùng A đến D, tức cột điểm Toán.
- Kéo ô vuông nhỏ ở góc ô B2 xuống cuối danh sách để áp dụng cho các dòng còn lại.
Muốn lấy thêm điểm Văn, bạn chỉ cần đổi số 3 thành 4. Có một mẹo nhỏ: dùng hàm COLUMN() thay cho số cố định để công thức tự đổi cột khi kéo sang ngang, nhưng với người mới thì sửa tay từng số sẽ dễ kiểm soát hơn.
Sau khi điền xong, hãy chọn ngẫu nhiên ba bốn dòng và so lại với bảng gốc. Bước kiểm tra nhanh này giúp phát hiện lỗi vùng tra cứu từ sớm, trước khi bảng điểm được gửi đi đâu đó.
Lỗi #N/A thường gặp và cách sửa
Lỗi #N/A nghĩa là Excel không tìm thấy giá trị cần tra. Có vài nguyên nhân hay gặp, và hầu hết đều sửa được trong vài phút.
Nguyên nhân phổ biến nhất là dư khoảng trắng. Mã “HS01 ” (có dấu cách cuối) khác với “HS01” trong mắt Excel. Hãy bọc giá trị tra cứu trong hàm TRIM, ví dụ =VLOOKUP(TRIM(A2), …), hoặc làm sạch cột dữ liệu gốc.
Nguyên nhân thứ hai là sai kiểu dữ liệu. Một bên là số, một bên là văn bản trông giống số, nên Excel coi chúng là hai thứ khác nhau. Dấu hiệu nhận biết là ô bị lệch trái và có tam giác xanh ở góc. Bạn có thể chuyển về cùng kiểu bằng cách nhân với 1 hoặc dùng chức năng Text to Columns.
Nguyên nhân thứ ba là vùng tra cứu không khoá, khiến vùng trượt xuống và bỏ sót dòng cuối. Kiểm tra lại dấu $ trong công thức sẽ cho bạn câu trả lời.
Nếu muốn ô hiển thị gọn gàng thay vì #N/A, hãy bọc công thức trong IFERROR, chẳng hạn =IFERROR(VLOOKUP(A2, Diem!$A$2:$D$41, 3, FALSE), “Chưa có điểm”). Cách này tiện cho báo cáo, nhưng đừng che lỗi khi bạn còn đang kiểm tra dữ liệu, vì bạn sẽ không thấy mình sai ở đâu.
Khi nào nên chuyển sang INDEX MATCH
VLOOKUP có hai giới hạn rõ rệt. Thứ nhất, nó chỉ tìm ở cột đầu tiên của vùng. Thứ hai, số thứ tự cột là con số cố định, nên chèn thêm một cột vào giữa bảng có thể làm công thức trả sai dữ liệu mà không báo lỗi.
Khi bảng của bạn hay thay đổi cấu trúc, hoặc khi cột khoá nằm bên phải cột kết quả, bạn nên cân nhắc cặp hàm INDEX và MATCH. Cặp hàm này linh hoạt hơn nhiều, và chúng tôi đã viết riêng một bài về hàm INDEX MATCH trong Excel để bạn so sánh.
Nếu bạn muốn xử lý nhiều điều kiện cùng lúc, bài công thức mảng trong Excel cho người mới sẽ là bước tiếp theo hợp lý. Bạn cũng có thể đọc mô tả chính thức về hàm này trong trang hỗ trợ Excel của Microsoft, nơi có đủ cú pháp cho từng phiên bản.
Việc nhỏ để bạn tự làm
Hãy tạo một bảng mười học sinh với mã, tên và hai cột điểm. Sau đó dựng sheet thứ hai chỉ có mã và dùng VLOOKUP để kéo điểm sang. Cố tình gõ sai một mã để xem lỗi #N/A xuất hiện, rồi sửa bằng IFERROR. Làm xong bài tập này, bạn sẽ nhớ cú pháp lâu hơn nhiều so với việc chỉ đọc.
Nếu vẫn thấy mơ hồ, đừng lo. Hầu hết chúng ta đều vấp ở số thứ tự cột trong vài lần đầu, và đó là cách học bình thường của hàm này.
