Bạn có bao giờ có bảng tính lớn với dữ liệu trong Excel và cần cách dễ dàng lọc và trích xuất thông tin cụ thể từ nó? Nếu bạn học cách dùng VLOOKUP trong Excel, bạn có thể tra cứu đó chỉ với một hàm Excel mạnh mẽ đơn lẻ.
Hàm VLOOKUP trong Excel khiến nhiều người sợ vì nó có nhiều tham số và có nhiều cách dùng. Trong bài này bạn sẽ học tất cả cách dùng VLOOKUP trong Excel và tại sao hàm này mạnh mẽ.
Tham số VLOOKUP trong Excel
Khi bạn bắt đầu gõ =VLOOKUP( vào bất kỳ ô nào trong Excel, bạn sẽ thấy popup hiển thị tất cả tham số hàm có sẵn.
Hãy kiểm tra từng tham số và ý nghĩa của chúng.
- lookup_value: Giá trị bạn đang tìm từ bảng tính
- table_array: Phạm vi ô trong sheet bạn muốn tìm kiếm
- col_index_num: Cột bạn muốn kéo kết quả từ đó
- [range_lookup]: Chế độ khớp (TRUE = gần đúng, FALSE = chính xác)
Bốn tham số này cho phép bạn làm nhiều tìm kiếm hữu ích khác nhau cho dữ liệu bên trong tập dữ liệu rất lớn.
Ví dụ VLOOKUP Excel đơn giản
VLOOKUP không phải hàm Excel cơ bản bạn có thể đã học, nên hãy xem ví dụ đơn giản để bắt đầu.
Cho ví dụ sau, chúng ta sẽ dùng bảng tính lớn điểm SAT cho trường học tại Mỹ. Bảng tính chứa hơn 450 trường cùng điểm SAT riêng lẻ cho đọc, toán, và viết. Tự do tải xuống để theo dõi. Có kết nối ngoài kéo dữ liệu, nên bạn sẽ nhận cảnh báo khi mở tệp, nhưng an toàn.
Sẽ rất tốn thời gian tìm qua tập dữ liệu lớn như vậy để tìm trường bạn quan tâm.
Thay vào đó, bạn có thể tạo một biểu mẫu đơn giản trong ô trống bên cạnh bảng. Để thực hiện tìm kiếm, chỉ tạo một trường cho Trường học, và ba trường thêm cho điểm đọc, toán, và viết.
Tiếp theo, bạn sẽ cần dùng hàm VLOOKUP trong Excel để làm cho ba trường này hoạt động. Trong trường Reading (Đọc), tạo hàm VLOOKUP như sau:
- Gõ =VLOOKUP(
- Chọn trường Trường học, trong ví dụ này là I2. Gõ dấu phẩy.
- Chọn toàn bộ phạm vi ô chứa dữ liệu bạn muốn tra cứu. Gõ dấu phẩy.
Lưu ý: Hàm VLOOKUP trong Excel chỉ tìm kiếm qua các ô bên phải cột tìm kiếm. Trong ví dụ này, cột tên trường cần ở bên trái dữ liệu bạn tra cứu.
- Tiếp theo, để lấy điểm Reading, bạn cần chọn cột thứ 3 từ cột ngoài cùng bên trái đã chọn. Vì vậy, gõ 3 rồi gõ dấu phẩy nữa.
- Cuối cùng, gõ FALSE cho khớp chính xác, và đóng hàm bằng ).
Công thức VLOOKUP cuối cùng nên trông như sau:
=VLOOKUP(I2,B2:G461,3,FALSE)
Khi bạn nhấn Enter lần đầu và hoàn thành hàm, bạn sẽ thấy trường Reading chứa #N/A.
Điều này vì trường Trường học trống và không có gì cho VLOOKUP tìm. Tuy nhiên, nếu bạn nhập tên bất kỳ trường trung học bạn muốn tra cứu, bạn sẽ thấy kết quả đúng từ hàng đó cho điểm Reading.
Cách xử lý VLOOKUP phân biệt chữ hoa thường (Case-Sensitive)
Bạn có thể nhận thấy nếu bạn không gõ tên trường cùng chữ hoa/thường như cách nó liệt kê trong tập dữ liệu, bạn sẽ không thấy kết quả.
Điều này vì hàm VLOOKUP phân biệt chữ hoa thường (case sensitive). Điều này có thể phiền toái, đặc biệt cho tập dữ liệu rất lớn nơi cột bạn tìm kiếm không nhất quán về cách viết hoa.
Để khắc phục, bạn có thể ép giá trị tìm kiếm chuyển thành chữ thường trước khi tra cứu. Để làm vậy, tạo cột mới bên cạnh cột bạn tìm. Gõ hàm:
=TRIM(LOWER(B2))
Điều này sẽ viết thường tên trường và loại bỏ ký tự thừa (khoảng trắng) bên trái hoặc phải tên.
Giữ phím Shift và đặt con trỏ chuột ở góc dưới bên phải ô đầu cho đến khi nó biến thành hai đường ngang. Nhấp đúp chuột để tự động điền toàn bộ cột.
Cuối cùng, vì VLOOKUP sẽ cố dùng công thức thay vì văn bản trong các ô này, bạn cần chuyển tất cả thành chỉ giá trị (values only). Để làm vậy, sao chép toàn bộ cột, chuột phải vào ô đầu, và dán chỉ giá trị (paste values only).
Bây giờ tất cả dữ liệu đã được dọn dẹp trong cột mới, sửa đổi nhẹ hàm VLOOKUP trong Excel để dùng cột mới này thay vì cột trước bằng cách bắt đầu phạm vi tra cứu ở C2 thay vì B2.
=VLOOKUP(I2,C2:G461,3,FALSE)
Bây giờ bạn sẽ thấy nếu bạn luôn gõ tìm kiếm bằng chữ thường, bạn luôn có kết quả tìm kiếm tốt.
Đây là mẹo Excel tiện lợi để khắc phục việc VLOOKUP phân biệt chữ hoa thường.
VLOOKUP Khớp gần đúng (Approximate Match)
Mặc dù ví dụ khớp chính xác (exact match) trong phần đầu bài khá trực tiếp, khớp gần đúng (approximate match) hơi phức tạp hơn.
Khớp gần đúng tốt nhất dùng để tìm kiếm qua phạm vi số (number ranges). Để làm đúng cách, phạm vi tìm kiếm cần được sắp xếp đúng. Ví dụ tốt nhất là hàm VLOOKUP tìm chữ cái điểm (letter grade) tương ứng với điểm số (numeric grade).
Nếu giáo viên có danh sách dài điểm bài tập về nhà học sinh trong năm với cột trung bình cuối, sẽ tốt nếu chữ cái điểm tương ứng với điểm cuối đó tự động xuất hiện.
Điều này có thể với hàm VLOOKUP. Tất cả yêu cầu là bảng tra cứu (lookup table) bên phải chứa chữ cái điểm thích hợp cho mỗi phạm vi điểm số.
Bây giờ, dùng hàm VLOOKUP và khớp gần đúng, bạn có thể tìm chữ cái điểm thích hợp tương ứng với phạm vi số đúng.
Trong hàm VLOOKUP này:
- lookup_value: F2, điểm trung bình cuối
- table_array: I2:J8, Phạm vi tra cứu điểm chữ
- index_column: 2, cột thứ hai trong bảng tra cứu
- [range_lookup]: TRUE, khớp gần đúng
Khi bạn hoàn thành hàm VLOOKUP trong G2 và nhấn Enter, bạn có thể điền các ô còn lại dùng cách tương tự như phần trước. Bạn sẽ thấy tất cả điểm chữ được điền đúng.
Lưu ý hàm VLOOKUP trong Excel tìm từ cuối dưới của phạm vi điểm với điểm chữ gán đến đỉnh phạm vi điểm chữ tiếp theo.
Vì vậy, "C" cần là chữ gán cho phạm vi dưới (75), và B gán cho đáy (tối thiểu) phạm vi của nó. VLOOKUP sẽ "tìm" kết quả cho 60 (D) là giá trị gần đúng nhất cho bất cứ thứ gì giữa 60 đến 75.
VLOOKUP trong Excel là hàm rất mạnh mẽ có sẵn lâu rồi. Nó cũng hữu ích tìm giá trị khớp bất cứ đâu trong workbook Excel.
Tuy nhiên, hãy nhớ người dùng Microsoft có đăng ký Office 365 hàng tháng giờ có quyền truy cập hàm XLOOKUP mới hơn. Hàm này có nhiều tham số hơn và linh hoạt hơn. Người dùng đăng ký bán niên sẽ phải đợi cập nhật ra mắt tháng 7/2020.