Nội dung chính
Hàm VLOOKUP là một trong những hàm phổ biến nhất trong Microsoft Excel. Hướng dẫn này của Học Excel Online sẽ hỗ trợ mọi cấp độ người dùng Excel về cách sử dụng và cú pháp của hàm VLOOKUP, thông qua 4 Ví dụ hàm Vlookup dưới đây.
Hình 1. Ví dụ hàm VLOOKUP: VLOOKUP với 2 bảng tra cứu
Cú pháp của hàm VLOOKUP
Hàm Vlookup có cú pháp như sau:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Các tham số của hàm VLOOKUP là:
- lookup_value – giá trị mà chúng ta muốn tìm kiếm và tìm thấy trong table_array
- table_array – phạm vi ô trong bảng nguồn chứa dữ liệu chúng ta muốn lấy
- col_index_num – số cột trong table_array tương ứng với thông tin chúng ta muốn lấy, liên quan đến lookup_value
- [range_lookup] – tùy chọn; giá trị là TRUE hoặc FALSE
- nếu TRUE hoặc bị bỏ qua, hàm VLOOKUP trả về kết quả khớp chính xác hoặc gần đúng
- nếu FALSE, hàm VLOOKUP sẽ chỉ tìm thấy kết quả khớp chính xác
Khi sử dụng hàm VLOOKUP, chúng ta chỉ cần nhớ bốn tham số cần thiết:
WHAT, WHERE, Column Number, Closet Match
- lookup_value – tham số WHAT, đây là những gì chúng ta muốn tìm kiếm
- table_array – tham số WHERE, đây là nơi chúng ta muốn tìm, nơi lookup_value có thể được tìm thấy trong cột ngoài cùng bên trái
- col_index_num – COLUMN NUMBER, đây là số cột dữ liệu mà chúng ta muốn trích xuất, bắt đầu đếm từ cột ngoài cùng bên trái của table_array
- [range_lookup] – CLOSEST MATCH; nếu TRUE, chúng ta muốn tìm khớp gần nhất hoặc chính xác, nếu FALSE, chúng ta chỉ muốn tìm khớp chính xác
Các hàm trong Excel của Học Excel Online đã cập nhật đầy đủ cú pháp và hoàn cảnh sử dụng của các hàm thông dụng.
Các ứng dụng của hàm VLOOKUP
Trong bài này chúng ta cùng tìm hiểu một số ứng dụng hữu ích của hàm VLOOKUP như:
- Tra cứu nhanh ngày sinh nhật của nhân viên
- Tính điểm
- Gộp hai bảng thành một hàm VLOOKUP có hai bảng
Tra cứu nhanh ngày sinh nhật nhân viên
Hình 2. Ví dụ hàm VLOOKUP: Tra cứu ngày sinh của nhân viên
Giả sử chúng ta có một danh sách nhân viên và ngày sinh của họ, cách nhanh nhất để tra cứu ngày sinh với ID nhân viên là sử dụng hàm VLOOKUP.
Bước 1. Chọn ô F3
Bước 2. Nhập công thức: =VLOOKUP(F2,B3:C13,2,FALSE)
Bước 3: Nhấn ENTER
Như đã thảo luận ở trên, hàm VLOOKUP có bốn tham số. Đối với ví dụ này, chúng ta muốn tìm F2 (What) trong B3:C13 (Where) và lấy 2 (Column Number) bằng cách sử dụng FALSE (Exact Match). Số cột là 2 vì ngày sinh nằm trong cột thứ hai của mảng bảng B3:C13.
Kết quả là, chúng ta đã tìm thấy một kết quả khớp chính xác cho ID nhân viên 19473 dọc theo cột B và chuyển sang cột thứ hai bên phải, lấy ngày sinh là “4/7/1993”.
Hình 3. Ví dụ hàm VLOOKUP để lấy ngày sinh của nhân viên
Chỉ trong vài giây, chúng ta có thể có được ngày sinh của một nhân viên được cung cấp ID nhân viên bằng hàm VLOOKUP. Các phương pháp thay thế sẽ là xem qua danh sách lần lượt hoặc lọc danh sách để hiển thị ID nhân viên 19473, cả hai đều tiêu tốn thời gian quý báu và dễ xảy ra lỗi.
Ví dụ hàm Vlookup: Tính điểm
Hình 4. Ví dụ hàm Vlookup: Tính điểm bằng hàm VLOOKUP
Giả sử chúng ta được cung cấp một bảng với hai cột: Cut-off Score (cột B) và Grade (cột C). Chúng ta muốn có thể nhanh chóng xác định điểm tương đương cho một số điểm nhất định. Chúng ta có thể thực hiện điều này với hàm VLOOKUP và sử dụng khớp gần đúng.
Bước 1. Chọn ô F3
Bước 2. Nhập công thức: =VLOOKUP(F2,B3:C7,2,TRUE)
Bước 3: Nhấn ENTER
Hình 5. Sử dụng hàm VLOOKUP với đối sánh chính xác để tính điểm
Công thức VLOOKUP của chúng ta tìm kiếm điểm số 95 dọc theo cột B. Khi tìm thấy điểm số, công thức này sẽ chuyển sang cột thứ hai bên phải và nhận được điểm “A”. Cuối cùng, chúng ta có thể xác định cấp tương đương cho 95 bằng cách sử dụng hàm VLOOKUP.
Sử dụng khớp gần đúng hoặc gần đúng nhất sẽ cho phép chúng ta lấy điểm cho các điểm không được liệt kê trong bảng B3:C8. Hãy thử nhập 93 vào ô F2.
Hình 6. Sử dụng hàm VLOOKUP với đối sánh gần đúng để tính điểm
Khi công thức không tìm thấy giá trị tra cứu trong bảng, nó sẽ tìm kiếm giá trị gần nhất nhưng nhỏ hơn giá trị tra cứu. Trong trường hợp này, công thức của chúng ta được coi là điểm 85 và trả về điểm tương đương là B. Nếu chúng ta sử dụng khớp chính xác thay thế, công thức của chúng ta sẽ trả về lỗi. Xem ví dụ dưới đây:
Hình 7. Hàm VLOOKUP với khớp chính xác trả về lỗi khi không tìm thấy kết quả phù hợp
Hợp nhất hai bảng tính thành một
Hình 8. Ví dụ về công thức VLOOKUP: Hợp nhất hai bảng thành một
Ở đây chúng ta có hai bảng với hai cột mỗi bảng. Bảng 1 chứa “Name” và “Group” trong khi Bảng 2 chứa “Name” và “Color”. Chúng ta muốn hợp nhất hai bảng và tạo ra một bảng có ba cột: Name, Group và Color. Chúng ta có thể dễ dàng hợp nhất hai cột bằng cách sử dụng hàm VLOOKUP với kết quả khớp chính xác.
Bước 1. Thêm cột thứ ba trong Bảng 1 có tên “Color”
Hình 9. Thêm một cột để hợp nhất hai bảng
Bước 2. Nhập công thức này vào D3: =VLOOKUP(B3,$F$3:$G$7,2,FALSE)
Bước 3: Nhấn ENTER
Bước 4: Sao chép công thức trong ô D3 sang ô D4:D7 bằng cách nhấp vào dấu “+ ” ở góc dưới cùng bên phải của ô D3 và kéo nó xuống.
Đô la ký hiệu “$” trong công thức sửa các ô để chúng ta có thể dễ dàng sao chép và dán công thức vào các ô khác.
Hình 10. Ví dụ hàm VLOOKUP để hợp nhất hai bảng
Bây giờ chúng ta đã hợp nhất thành công hai bảng thành một, như được hiển thị ở trong B2:D7.
VLOOKUP với hai bảng tra cứu
Hình 11. Ví dụ hàm Vlookup: VLOOKUP với 2 bảng tra cứu
Giả sử chúng ta có hai bảng mỗi bảng có hai cột, Movie (cột B) và Ticket Price (cột C). Bảng 1 dành cho vé “Premier”, trong khi Bảng 2 dành cho “Deluxe”. Trong ô F3 và F4, chúng ta nhập tiêu chí cho Type và Movie. Chúng ta muốn tra cứu bộ phim trong bảng thích hợp và lấy giá vé. Giá trị cho Type sẽ xác định bảng nào chúng ta sẽ sử dụng trong tra cứu.
Chúng ta có thể sử dụng công thức bằng hàm VLOOKUP và hàm IF.
Bước 1. Chọn ô F5
Bước 2. Nhập công thức: =VLOOKUP(F4,IF(F3=B2,B4:C6,B10:C12),2,FALSE)
Bước 3: Nhấn ENTER
Hình 12. Ví dụ hàm VLOOKUP và IF với 2 bảng tra cứu
Công thức của chúng ta tìm kiếm “A Star is Born” trong bảng cho “Premier”, B4:B6 và trả về giá vé tương ứng là $14. Chìa khóa ở đây là bằng cách kết hợp các hàm VLOOKUP và IF. IF đánh giá type và trả về B4:B6 dưới dạng table_array nếu F3 bằng B2 hoặc “Premier”; nếu không, nó trả về B10:C12.
Bốn ví dụ hàm Vlookup chúng ta vừa học chỉ là một vài ứng dụng của hàm VLOOKUP. Trên thực tế thì hàm này có tiềm năng cải thiện năng suất và xử lý dữ liệu khi được vận dụng thành thạo.
