Bạn đã bao giờ có một bảng tính lớn chứa dữ liệu trong Excel và cần một cách dễ dàng để lọc và trích xuất thông tin cụ thể từ đó chưa? Nếu bạn tìm hiểu cách sử dụng VLOOKUP trong Excel, bạn có thể thực hiện việc tra cứu này chỉ bằng một hàm Excel mạnh mẽ duy nhất.
Hàm VLOOKUP trong Excel khiến nhiều người sợ hãi vì nó có rất nhiều tham số và có nhiều cách sử dụng. Trong bài viết này, bạn sẽ tìm hiểu tất cả các cách bạn có thể sử dụng VLOOKUP trong Excel và tại sao hàm này lại mạnh mẽ đến vậy.
Nội dung bài viết
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 một cửa sổ bật lên hiển thị tất cả các tham số hàm có sẵn.
Chúng ta hãy xem xét từng thông số này và ý nghĩa của chúng.
- tra cứu_value: Giá trị bạn đang tìm kiếm từ bảng tính
- bảng_mảng: Phạm vi ô trong sheet muốn tìm kiếm
- col_index_num: Cột nơi bạn muốn lấy 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 thực hiện nhiều tìm kiếm hữu ích khác nhau đối với dữ liệu bên trong các tập dữ liệu rất lớn.
Một ví dụ Excel VLOOKUP đơn giản
VLOOKUP không phải là một trong những hàm Excel cơ bản mà bạn có thể đã học, vì vậy hãy xem một ví dụ đơn giản để bắt đầu.
Trong ví dụ sau, chúng tôi sẽ sử dụng một bảng tính lớn về điểm SAT cho các trường học ở Hoa Kỳ. Bảng tính này chứa hơn 450 trường cùng với điểm SAT riêng cho môn đọc, toán và viết. Hãy tải về để theo dõi nhé. Có kết nối bên ngoài kéo dữ liệu nên khi mở file sẽ có cảnh báo nhưng an toàn.
Sẽ rất tốn thời gian khi tìm kiếm trong một tập dữ liệu lớn như vậy để tìm ra ngôi trường mà bạn quan tâm.
Thay vào đó, bạn có thể tạo một biểu mẫu đơn giản trong các ô trống ở cạnh bảng. Để tiến hành tìm kiếm này, chỉ cần tạo một trường cho Trường học và ba trường bổ sung cho điểm đọc, toán và viết.
Tiếp theo, bạn sẽ cần sử dụng hàm VLOOKUP trong Excel để làm cho ba trường này hoạt động. bên trong Đọc trường, hãy tạo hàm VLOOKUP như sau:
- Kiểu =VLOOKUP(
- Chọn trường Trường học, trong ví dụ này là I2. Nhập dấu phẩy.
- Chọn toàn bộ dãy ô chứa dữ liệu bạn muốn tra cứu. Nhập dấu phẩy.
Khi bạn chọn phạm vi, bạn có thể bắt đầu từ cột bạn đang sử dụng để tra cứu (trong trường hợp này là cột tên trường), sau đó chọn tất cả các cột khác columns và các hàng chứa dữ liệu.
Ghi chú: Hàm VLOOKUP trong Excel chỉ có thể 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 nằm ở bên trái dữ liệu bạn đang tra cứu.
- Tiếp theo, để lấy điểm Đọc, bạn cần chọn cột thứ 3 từ cột được chọn ngoài cùng bên trái. Vì vậy, hãy gõ một 3 rồi gõ một dấu phẩy khác.
- Cuối cùng, gõ SAI để có kết quả khớp chính xác và đóng hàm bằng dấu ).
Hàm VLOOKUP cuối cùng của bạn sẽ trông giống như thế này:
=VLOOKUP(I2,B2:G461,3,FALSE)
Khi bạn nhấn lần đầu tiên Enter và hoàn thành chức năng, bạn sẽ nhận thấy trường Đọc sẽ chứa một #N/A.
Điều này là do trường Trường học trống và không có gì để hàm VLOOKUP tìm thấy. Tuy nhiên, nếu nhập tên trường THPT bất kỳ mà bạn muốn tra cứu, bạn sẽ thấy kết quả chính xác từ hàng đó cho điểm Đọc.
Cách xử lý VLOOKUP phân biệt chữ hoa chữ thường
Bạn có thể nhận thấy rằng nếu bạn không nhập tên trường giống như trường hợp được liệt kê trong tập dữ liệu, bạn sẽ không thấy bất kỳ kết quả nào.
Điều này là do hàm VLOOKUP phân biệt chữ hoa chữ thường. Điều này có thể gây khó chịu, đặc biệt đối với một tập dữ liệu rất lớn trong đó cột bạn đang tìm kiếm không nhất quán với cách viết hoa mọi thứ.
Để giải quyết vấn đề này, bạn có thể buộc nội dung bạn đang tìm kiếm chuyển sang chữ thường trước khi tra cứu kết quả. Để thực hiện việc này, hãy tạo một cột mới bên cạnh cột bạn đang tìm kiếm. Nhập hàm:
=TRIM(LOWER(B2))
Thao tác này sẽ viết thường tên trường và xóa mọi ký tự không liên quan (dấu cách) có thể ở bên trái hoặc bên phải của tên.
Giữ phím Shift và đặt con trỏ chuột lên góc dưới bên phải của ô đầu tiên cho đến khi nó chuyể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ố gắng sử dụng công thức thay vì văn bản trong các ô này nên bạn chỉ cần chuyển đổi tất cả chúng thành giá trị. Để thực hiện việc này, hãy sao chép toàn bộ cột, nhấp chuột phải vào ô đầu tiên và chỉ dán các giá trị.
Bây giờ tất cả dữ liệu của bạn đã được dọn sạch trong cột mới này, hãy sửa đổi một chút hàm VLOOKUP trong Excel để sử 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ẽ nhận thấy rằng nếu bạn luôn nhập tìm kiếm bằng chữ thường, bạn sẽ luôn nhận được kết quả tìm kiếm tốt.
Đây là một mẹo Excel hữu ích để khắc phục thực tế là VLOOKUP phân biệt chữ hoa chữ thường.
Kết quả gần đúng VLOOKUP
Mặc dù ví dụ LOOKUP đối sánh chính xác được mô tả trong phần đầu tiên của bài viết này khá đơn giản nhưng đối sánh gần đúng lại phức tạp hơn một chút.
Kết quả gần đúng được sử dụng tốt nhất để tìm kiếm trong phạm vi số. Để thực hiện điều này một cách chính xác, phạm vi tìm kiếm cần phải được sắp xếp hợp lý. Ví dụ tốt nhất về điều này là hàm VLOOKUP để tìm kiếm cấp chữ cái tương ứng với cấp số.
Nếu giáo viên có một danh sách dài các điểm bài tập về nhà của học sinh trong suốt cả năm với một cột trung bình cuối cùng, thì sẽ rất tuyệt nếu điểm chữ cái tương ứng với điểm cuối cùng đó tự động xuất hiện.
Điều này có thể thực hiện được với hàm VLOOKUP. Tất cả những gì cần thiết là một bảng tra cứu ở bên phải có chứa cấp chữ cái thích hợp cho từng phạm vi điểm bằng số.
Bây giờ, bằng cách sử dụng hàm VLOOKUP và kết quả khớp gần đúng, bạn có thể tìm được cấp chữ cái phù hợp tương ứng với dãy số chính xác.
Trong hàm VLOOKUP này:
- tra cứu_value: F2, điểm trung bình cuối cùng
- bảng_mảng: I2:J8, Phạm vi tra cứu cấp chữ cái
- cột_chỉ mục: 2, cột thứ 2 trong bảng tra cứu
- [range_lookup]: TRUE, kết quả gần đúng
Khi bạn hoàn thành chức năng VLOOKUP trong G2 và nhấn Enter, bạn có thể điền vào các ô còn lại bằng cách sử dụng phương pháp tương tự được mô tả ở phần trước. Bạn sẽ thấy tất cả các chữ cái được điền đúng.
Lưu ý rằng hàm VLOOKUP trong Excel tìm kiếm từ cuối phạm vi điểm có điểm chữ cái được chỉ định cho đến đầu phạm vi điểm chữ cái tiếp theo.
Vì vậy, “C” cần phải là chữ cái được gán cho phạm vi chữ cái thấp hơn (75) và B được gán ở dưới cùng (tối thiểu) trong phạm vi chữ cái của chính nó. VLOOKUP sẽ “tìm” kết quả cho 60 (D) là giá trị gần đúng nhất cho bất kỳ giá trị nào trong khoảng từ 60 đến 75.
VLOOKUP trong Excel là một hàm rất mạnh đã có từ lâu. Nó cũng hữu ích để tìm các giá trị khớp ở bất kỳ đâu trong sổ làm việc Excel.
Tuy nhiên, hãy nhớ rằng người dùng Microsoft đăng ký Office 365 hàng tháng hiện có quyền truy cập vào chức năng XLOOKUP mới hơn. Chức năng này có nhiều tham số hơn và tính linh hoạt bổ sung. Người dùng đăng ký nửa năm sẽ phải đợi bản cập nhật ra mắt vào tháng 7 năm 2020.
