Do you have a value in Excel file which you want to find a match in a table and then show the correspondent value that reside in the table? Then VLOOKUP is here for you. You can use the VLOOKUP function to search the first column of a table, and then return a value from any cell on the same row of the match value.
VLOOKUP has four arguments of which the first three are compulsory, and the last one is optional, but defaults to TRUE if you leave it out :
VLOOKUP(Lookup_values,Table_array,Col_index_num,[range_lookup])
- Lookup_value – Which value are you looking for? This is the value which Excel will look for a match in the leftmost column of your lookup table. In our example we are looking for income in G3 (7,500)
- Table_array – This is the lookup table, where you want to search for your value. If you plan to copy your VLOOKUP formula, you may want to use absolute references to “lock” the range. In our example, the table array is B3:D7 ( that is the employee tax table)
- Col_index_num – Which column contain the search result? Count over from the first column to figure out what this number should be, starting with 1.
- [range_lookup]) – Should the lookup value be an exact match (FALSE or 0) or is an approximate match (TRUE or 1) okay if an exact match doesn’t exist? For TRUE, sort the leftmost column in ascending order for correct results.
A classic use of VLOOKUP is the computation of a PAYEE tax from an income tax table.
Case:
We want to look for the employee income (G3) in the employee tax table (B3:D7) and return the corresponding row value in column 3 (D), which is the tax rate. We are not looking for the exact match (TRUE) since the tax table has values in range (From-To).
The results:
The formula looked for 7,500 from tax table and return 20% tax rate. This is because, we used TRUE in the last augment. If you put FALSE or 0, the formula will return #N/A. The use of FALSE would make sense when you are picking a unique value such as Account number, customer ID etc. See example 2 below.
Assuming you have the list of customers’ ID and Sales volume in a table, however you need the customer name and country from another table. VLOOKUP is here to help you again.
The formula in Cell I3 is =VLOOKUP(G3,$B$3:$D$6,2,FALSE) and
J3 is =VLOOKUP(G3,$B$3:$D$6,3,0)
The I3 formula searches for the value 21103 in the first column of the range B3:D6, and then returns the value that is contained in the second column of the range and on the same row as the lookup value (“John”).
Guest what the V stands for in the VLOOKUP!……Yes, It stands for Vertical? There is a sister function called HLOOKUP and it is used when your comparison values are located in a column to the left of the data that you want to find. We hope to write about that in another article.
You can download the working Excel file here……
Your comments are welcome. Additionally, you can learn more about VLookup on Spreadsheeto