Hello Friends its Sachin here with a new blog on V lookup Function of excel. In this tutorial we are going to learn the following things about V Lookup function
- Syntax and Insight of V lookup
- Uses with Practical example of V lookup with Exact match
- Uses with Practical example of V lookup with Approximate match
- Things to be taken care of
Syntax and insight of V lookup
In its simplest form, the V LOOKUP function says:
=V lookup(lookup value, table array, column index_num, [range lookup]
There are four piece of information present in the formula
1 – Lookup value – The value you want to lookup in the another table
2. – Table array – The table in which you want to lookup the Lookup value
3. – Column Index – the couloun number from the table from which the value will return in case it find the lookup value
4. – Range lookup – Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don’t specify anything, the default value will always be TRUE or approximate match.
Uses with Practical example of V lookup with exact match
When to use V lookup with exact match – If you want to search some value in another table so as to find any data related to value searched in another table then V Lookup can do this
Exact match – it means lookup value should exactly match i:e even a difference of .01 will not be accepted.
False or 0 is used in range lookup for telling V lookup that its a exact match
Example -1 V lookup to get price of product
Explanation – rule 1 means V lookup always search the lookup value from the left most column so keep arrange your data in that way
once it searched the lookup value it will go to 3rd column of the table return the corresponding value which here is price of T shirt 200 Rs
Example -2 V lookup to get Employee salary
Explanation – here in this example we have used V lookup to find the information of employee from the database i:e lookup table
Uses with Practical example of V lookup with Approximate match
First let us understand what is approximate match – in case the lookup value is Text then there can be only exact match but if the lookup value is number than there can be two cases – exact match or approx match. Exact match we have already discussed
Approximate match – it means first the lookup formula will look for the number which is greater than the lookup number and then it will move one cell upside and return the value present in column index.
its generally used when we have range based table
Lets understand it with a example
Example – 1 Finding the rate of commission with the help of commission table
Explanation – As its visible in the image below this formula work in 3 steps
1.) finding the number greater than Lookup number
2.) Move one cell up
3.) return the value in prescribed column number
Things to be taken care of while using Vlookup
1.) In case of duplicate values in table array it will always give result of lookup value which comes first
2.) the formula will search the lookup value from the leftmost column of the lookup table.
3.) V lookup is non case sensitive formula
4.) in case it doesn’t found the lookup value it will return to NA error
5.) In case of V lookup Approximate formula arrange the table in an ascending order otherwise it will give false result
5.) the formula can be combined with match formula to build a two way lookup which we will discuss in the another blog
Thank you for reading the blog
In case of any query kindly comment upon here and tell us about your opinion on the blog.