Buzz
Vlookup is a function used to search for values within columns, widely adopted by many users. You may have heard of Vlookup, but understanding how to use it effectively might still elude you. This article guides you on using the Vlookup function in Excel.
Description
The Vlookup function is used to search for values within columns, aiming to find a value in the first column from the left of a data table. If found, the function returns a value from one of the subsequent columns in the same row as the value in the first column specified by you.
Function commonly used to populate the main table with data sourced from the secondary table.
Syntax of the Vlookup Function
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Where:
- lookup_value: The value used for searching, which can be a string, a reference, or any value from the main table. If the lookup_value is less than the smallest value in the first column of the reference table table_array, the Vlookup function returns an error value.
- table_array: The reference table or table containing values to search, where values in the first column of table_array are searched by the lookup_value. This can be text, numbers, or logical values.
- col_index_num: The column index in table_array from which to retrieve the value returned to the main table. If col_index_num is less than 1 or greater than the number of columns in table_array, the function returns an error value.
- range_lookup: A logical value that helps you specify whether the Vlookup function should perform an exact match or an approximate match.
+ If range_lookup is TRUE or omitted, the function will search approximately for values in the first column of the table_array, which must be sorted to prevent Vlookup from returning incorrect results.
+ If range_lookup is FALSE, the function will search exactly for values in the first column of the table_array, without needing any specific sorting order.
Example:
- Performing an approximate lookup.
Given the following dataset, you are required to fill in information for Grade of Semester and Symbol based on the information from the Grade table.
- Fill in information for the Grade of Semester column, input the formula as follows:
First, select the cell under Grade of Semester for SV01, input the formula =VLOOKUP(E6, then use the mouse to select (highlight) the secondary table Grade, then release the mouse and press the F4 key, next input ,2,1).
The formula you will receive is as follows: =VLOOKUP(E6,$E$12:$G$16,2,1).
In this context:
E6 represents the value to be searched (e.g., in the example, it's 80).
$E$12:$G$16 stands for the table of information to be searched (e.g., in the example, it's the Grade table).
2 indicates column 2 in the table of information to be searched, where data from column 2 will be filled into the main table (e.g., in the example, it's the Grade of Semester column in the Grade table).
1 denotes a relative lookup.
- Fill in the information for the Symbol column, using a similar formula as the Term Classification column, but with the col_index_num value in the VLookup function set to 3, corresponding to the column from which values will be retrieved in the Classification table, which is the Symbol column.
=VLOOKUP(E6,$E$12:$G$16,3,1)
- For an exact lookup, simply change the range_lookup value to 0 or FALSE. However, if the lookup_value is not found in the first column of the reference table, the function will return an error.
With the simple example provided, it's hoped that you'll gain a clearer understanding of how to use the Vlookup function in Excel. Depending on your specific requirements, you can utilize Vlookup to handle data in your Excel spreadsheets. Wishing you success!
