Tải xuống sổ làm việc mẫu
Hướng dẫn này trình bày cách sử dụng Hàm VLOOKUP trong Excel trong Excel để tra cứu một giá trị.
Tổng quan về hàm VLOOKUP
Hàm Vlookup Vlookup là viết tắt của tra cứu theo chiều dọc. Nó tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng. Sau đó, trả về một giá trị một số cột được chỉ định ở bên phải từ giá trị được tìm thấy. Nó giống như một hlookup, ngoại trừ nó tìm kiếm các giá trị theo chiều dọc thay vì chiều ngang.
(Chú ý cách các đầu vào công thức xuất hiện)
Cú pháp và đối số của hàm VLOOKUP:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
lookup_value - Giá trị bạn muốn tìm kiếm.
table_array - Phạm vi dữ liệu chứa cả giá trị bạn muốn tìm kiếm và giá trị bạn muốn vlookup trả về. Cột chứa các giá trị tìm kiếm phải là cột ngoài cùng bên trái.
col_index_num - Số cột của phạm vi dữ liệu mà bạn muốn trả về giá trị từ đó.
range_lookup - Đúng hay sai. FALSE tìm kiếm một kết quả phù hợp chính xác. TRUE tìm kiếm kết quả phù hợp gần nhất nhỏ hơn hoặc bằng giá trị lookup_value. Nếu TRUE được chọn, cột ngoài cùng bên trái (cột tra cứu) phải được sắp xếp tăng dần (thấp nhất đến cao nhất).
Hàm VLOOKUP là gì?
Là một trong những hàm cũ hơn trong thế giới bảng tính, hàm VLOOKUP được sử dụng để làm Vsai lầm Các bản tra cứu. Nó có một số hạn chế thường được khắc phục với các chức năng khác, chẳng hạn như INDEX / MATCH. Tuy nhiên, nó có lợi ích là một hàm duy nhất có thể tự mình thực hiện tra cứu đối sánh chính xác. Nó cũng có xu hướng là một trong những chức năng đầu tiên mà mọi người tìm hiểu về.
Ví dụ cơ bản
Hãy xem một mẫu dữ liệu từ sổ điểm. Chúng tôi sẽ giải quyết một số ví dụ để trích xuất thông tin cho các sinh viên cụ thể.
Nếu chúng ta muốn tìm Bob thuộc lớp nào, chúng ta sẽ viết công thức:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, FALSE) |
Điều quan trọng cần nhớ là mục chúng tôi đang tìm kiếm (Bob), phải nằm trong cột đầu tiên của phạm vi tìm kiếm của chúng tôi (A2: C5). Chúng tôi đã nói với hàm rằng chúng tôi muốn trả về một giá trị từ 2NS , trong trường hợp này là cột B. Cuối cùng, chúng tôi đã chỉ ra rằng chúng tôi muốn thực hiện kết hợp chuẩn xác bằng cách đặt False làm đối số cuối cùng. Ở đây, câu trả lời sẽ là “Đọc”.
Mẹo phụ: Bạn cũng có thể sử dụng số 0 thay vì Sai làm đối số cuối cùng, vì chúng có cùng giá trị. Một số người thích điều này vì nó viết nhanh hơn. Chỉ biết rằng cả hai đều có thể chấp nhận được.
Dữ liệu đã thay đổi
Để thêm một số giải thích rõ ràng cho ví dụ đầu tiên của chúng tôi, mục tra cứu không nhất thiết phải nằm trong cột A của bảng tính của bạn, chỉ là cột đầu tiên trong phạm vi tìm kiếm của bạn. Hãy sử dụng cùng một tập dữ liệu:
Bây giờ, hãy tìm điểm cho lớp Khoa học. Công thức của chúng tôi sẽ là
| 1 | = VLOOKUP ("Khoa học", B2: C5, 2, FALSE) |
Đây vẫn là một công thức hợp lệ, vì cột đầu tiên trong phạm vi tìm kiếm của chúng tôi là cột B, là nơi tìm thấy cụm từ tìm kiếm của chúng tôi về “Khoa học”. Chúng tôi đang trả về một giá trị từ 2NS của phạm vi tìm kiếm, trong trường hợp này là cột C. Đáp án sau đó là “A-“.
Sử dụng ký tự đại diện
Hàm VLOOKUP hỗ trợ việc sử dụng các ký tự đại diện “*” an “?” khi thực hiện tìm kiếm. Ví dụ: giả sử chúng tôi đã quên cách đánh vần tên của Frank và chỉ muốn tìm kiếm một cái tên bắt đầu bằng “F”. Chúng tôi có thể viết công thức
| 1 | = VLOOKUP ("F *", A2: C5, 2, FALSE) |
Điều này sẽ có thể tìm thấy tên Frank ở hàng 5 và sau đó trả về giá trị từ 2NS cột tương đối. Trong trường hợp này, câu trả lời sẽ là "Khoa học".
Đối sánh không chính xác
Hầu hết thời gian, bạn sẽ muốn đảm bảo rằng đối số cuối cùng trong hàm VLOOKUP là False (hoặc 0) để bạn có được một đối sánh chính xác. Tuy nhiên, có một vài lần bạn có thể tìm kiếm kết quả không chính xác. Nếu bạn có danh sách dữ liệu được sắp xếp, bạn cũng có thể sử dụng hàm VLOOKUP để trả về kết quả cho mục giống nhau hoặc nhỏ nhất tiếp theo. Điều này thường được sử dụng khi xử lý các dải số ngày càng tăng, chẳng hạn như trong bảng thuế hoặc tiền thưởng hoa hồng.
Giả sử bạn muốn tìm thuế suất cho thu nhập được nhập vào ô D2. Công thức trong D4 có thể là:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
Sự khác biệt trong công thức này là đối số cuối cùng của chúng ta là "Đúng". Trong ví dụ cụ thể của chúng ta, chúng ta có thể thấy rằng khi cá nhân của chúng ta đầu vào thu nhập là $ 45,000 thì họ sẽ có thuế suất là 15%.
Ghi chú: Mặc dù chúng tôi thường muốn đối sánh chính xác với False làm đối số, nhưng bạn quên chỉ định 4NS trong một hàm VLOOKUP, mặc định là True. Điều này có thể khiến bạn nhận được một số kết quả không mong muốn, đặc biệt là khi xử lý các giá trị văn bản.
Cột động
VLOOKUP yêu cầu bạn đưa ra một đối số cho biết bạn muốn trả về giá trị từ cột nào, nhưng có thể xảy ra trường hợp bạn không biết cột đó sẽ ở đâu hoặc bạn muốn cho phép người dùng của mình thay đổi cột để trả về từ đó. Trong những trường hợp này, có thể hữu ích khi sử dụng hàm MATCH để xác định số cột.
Chúng ta hãy xem xét lại ví dụ về sách điểm của chúng ta, với một số đầu vào trong E2 và E4. Để lấy số cột, chúng ta có thể viết công thức
| 1 | = MATCH (E2, A1: C1, 0) |
Điều này sẽ cố gắng tìm vị trí chính xác của "Lớp" trong phạm vi A1: C1. Câu trả lời sẽ là 3. Biết được điều này, chúng ta có thể cắm nó vào một hàm VLOOKUP và viết một công thức trong E6 như sau:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Vì vậy, hàm MATCH sẽ đánh giá là 3 và điều đó yêu cầu hàm VLOOKUP trả về kết quả từ 3rd trong phạm vi A2: C5. Nhìn chung, sau đó chúng tôi nhận được kết quả mong muốn của chúng tôi là “C”. Công thức của chúng tôi hiện đang động ở chỗ chúng tôi có thể thay đổi cột cần xem hoặc tên để tìm kiếm.
Các giới hạn của hàm VLOOKUP
Như đã đề cập ở phần đầu của bài viết, nhược điểm lớn nhất của VLOOKUP là nó yêu cầu cụm từ tìm kiếm phải được tìm thấy ở cột bên trái nhất của phạm vi tìm kiếm. Mặc dù có một số thủ thuật ưa thích bạn có thể làm để khắc phục điều này, nhưng giải pháp thay thế phổ biến là sử dụng INDEX và MATCH. Sự kết hợp đó mang lại cho bạn sự linh hoạt hơn và đôi khi nó thậm chí có thể là một phép tính nhanh hơn.
VLOOKUP trong Google Trang tính
Hàm VLOOKUP hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
Ghi chú bổ sung
Sử dụng Hàm Vlookup để thực hiện tra cứu VERTICAL. VLOOKUP tìm kiếm một kết hợp chính xác (range_lookup = FALSE) hoặc so khớp gần nhất bằng hoặc nhỏ hơn giá trị lookup_value (range_lookup = TRUE, chỉ các giá trị số) trong hàng đầu tiên của table_array. Sau đó, nó trả về một giá trị tương ứng, n số hàng bên dưới so khớp.
Khi sử dụng hàm VLOOKUP để tìm một kết quả khớp chính xác, trước tiên, bạn xác định một giá trị nhận dạng mà bạn muốn tìm kiếm là lookup_value. Giá trị nhận dạng này có thể là SSN, ID nhân viên, tên hoặc một số định danh duy nhất khác.
Tiếp theo, bạn xác định phạm vi (được gọi là table_array) có chứa các số nhận dạng ở hàng trên cùng và bất kỳ giá trị nào mà bạn cuối cùng muốn tìm kiếm trong các hàng bên dưới nó. QUAN TRỌNG: Các số nhận dạng duy nhất phải ở hàng trên cùng. Nếu không, bạn phải di chuyển hàng lên trên cùng hoặc sử dụng MATCH / INDEX thay vì hàm VLOOKUP.
Thứ ba, xác định số hàng (row_index) sau đó table_array mà bạn muốn trở lại. Hãy nhớ rằng hàng đầu tiên, chứa các số nhận dạng duy nhất là hàng 1. Hàng thứ hai là hàng 2, v.v.
Cuối cùng, bạn phải cho biết nên tìm kiếm kết hợp chính xác (FALSE) hay kết hợp gần nhất (TRUE) trong range_lookup. Nếu tùy chọn đối sánh chính xác được chọn và không tìm thấy đối sánh chính xác, thì sẽ trả về lỗi (# N / A). Để công thức trả về trống hoặc “không tìm thấy” hoặc bất kỳ giá trị nào khác thay vì giá trị lỗi (# N / A), hãy sử dụng Hàm IFERROR với hàm VLOOKUP.
Để sử dụng Hàm VLOOKUP để trả về một tập hợp so khớp gần đúng: range_lookup = ĐÚNG. Tùy chọn này chỉ có sẵn cho các giá trị số. Các giá trị phải được sắp xếp theo thứ tự tăng dần.
Để thực hiện tra cứu theo chiều ngang, hãy sử dụng Hàm HLOOKUP để thay thế.
Ví dụ về VLOOKUP trong VBA
Bạn cũng có thể sử dụng hàm VLOOKUP trong VBA. Kiểu:application.worksheet Chức năng.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Đối với các đối số của hàm (lookup_value, v.v.), bạn có thể nhập chúng trực tiếp vào hàm hoặc xác định các biến để sử dụng thay thế.
Quay lại danh sách tất cả các hàm trong Excel
