What is Vlookup function?
The Vlookup function is a function in excel used to find values and return the results of searching for values by column. In addition, the Vlookup function can also be used to quickly and effectively statistic and search for data by column.
The Vlookup function is used to find a value by column, return the lookup value in a cell at a column in a lookup table.
The Vlookup function is one of the most popular and useful functions in Excel, but few people understand it. In this article, we will learn and how to use the Vlookup function proficiently through the most practical examples.
The structure of the vlookup function
=VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup])
Inside:
Lookup_value: is the value used to find
Table_array: is the criteria range to look for, the first column in table_array is the column to look for. Table_array can be the same or a different sheet as Lookup_value and can also be the same file or a different file as Lookup_value. Usually left in absolute address form
Col_index_num: Is the order of the column to get data on the value table to find. The first column of the data range is counted as 1
Range_lookup: A search type, consisting of 2 types TRUE and FALSE. (May or may not)
TRUE: Corresponding to 1 is relative search
FALSE: Corresponding to 0 is an absolute search that means the VLOOKUP function will search for exact matches.
Meaning of the Vlookup function
– Used to search for values by column (vertical row).
– Can be used alone or in combination with other functions: sum; If …
What is the function of the Vlookup function?
We use the Vlookup function to look up data in a table or a range by column in a predefined lookup table. Thus, the main function of the Vlookup function is to search for a value in a given table of values.
One of the most common functions of VLOOKUP is a data function, meaning that it will work against database tables or, more simply, a list of items. The list has many categories. You can make a chart about the company personnel, product categories, customer lists, or whatever.
Example using the Vlookup function
– Vlookup function for exact search
Example: Suppose, you have an employee data table, which stores employee code, first name, first name, and origin. Another table has employee codes and country columns are removed. Now you want to fill in the hometown information for each employee, how to do?
To compile country information for employees, in cell G4, we enter the exact detection formula as follows: = VLOOKUP (F4, $B$4:$D$10,3,0)
Inside:
F4: Is the value to be compared.
$B$4:$D$10: As a lookup table, the address of the search table must be the absolute address.
3: Number of columns of data on the search table.
0: Exact search type.
After completing the formula for cell G4, continue to scroll down to copy the formula for the rest of the staff. Observe the figure to better understand the formula.
– Vlookup function for relative search
Relative search can only be applied when the lookup value in table_array has been sorted by order (ascending or descending or alphabetically). For such tables you can use relative search, which is then the same as using the infinite IF function.
Example: Based on the chart of classification corresponding to a given score, conduct a academic rating for the students named in the list:
Now we will use the VLOOKUP function to enter a ranking for the students. You will notice that the Ranking Rules table has been sorted from low to high (weak to excellent) so in this case we can use relative search.
In cell E4, we enter the formula as : = VLOOKUP (D4,$B$14:$C$17,2,1)
Inside:
D4: Is the value to be compared.
$B$14:$C$17: The search table.
2: Number of columns of data on the search table.
1: Relative search type.
After completing the formula for cell E4, continue to drag down and copy the formula for the rest of the students. Observe the figure to better understand the relative finding and formula.
It is easy to see, when there is no result that matches the lookup value, the relative detection Vlookup function will get the next largest value less than it.
Some notes when using Vlookup function
The Vlookup function is part of the reference and search function
Reference functions used in formulas save us from having to waste time fixing formulas when calculated values change.
Relative address: The address is changed corresponding to each row and column when we copy the formula. (Example: B5 is the address of row 5 column B).
Absolute address: The address is fixed, unchanged when we copy the formula. (Example: $A$1- absolute address of a cell, $B$17:$C$20 – absolute address of an area)
To create an absolute address, you press F4, which will now have a dollar sign ($) in front of the column and row index.
In short, if it is an absolute address, you will see a dollar sign ($) before the column and row index.
– Mixed addresses: Mixed addresses are only fixed row or column addresses.
Freeze columns : For example, $A1, you see that the column index is fixed, and the row index is not.
Freeze rows : For example: A$1, you see the column index is not fixed, and the column index is fixed.
– The Vlookup function returns the first value found in the search table . If the leftmost column of the table contains duplicate values, the first value found will be retrieved.
– The vlookup function is not case sensitive.
– The vlookup function failed # N / A when searching.
When searching if the VLOOKUP function cannot find a match, it returns the # N / A error.
If you do an exact search, then the value you are looking for must match in the lookup table.
If you do a relative search, the value you are looking for must be greater than the minimum value in the lookup table.
This error is very common if you are not sure how to use the function.
See more details here:
