From all the questions I have been asked about and I have asked mostly are related to Excel Formulas If I filter even further I will find that I am looking for information on vlookup most of the times…
Here is one attempt from my end to explain what vlookup is all about.
Ya ya apart from the obvious answer that its an excel formula, it’s a search function!
It can find you matching information from a particular table of data – something similar to a hashtable or dictionary in data structures
You give it an identifier and it will find the corresponding value for it…
Can I get a VLOOKUP Example?
Of course! That’s what this blog is about
Jokes apart, here is an example of VLOOKUP
Problem Statement: Your boss asks you to find out the work experience in number of years of your team mates
Data you have:
Now you have to fill in this table to present it to your boss:
Now you’ll get the data in columns H, I from C and D – isn’t it? that’s simple
You have a way to do that using VLOOKUP too – here’s how
All you need for VLOOKUP to work is a unique value to look for in a table and it would return the column value you ask for…
the formula for VLOOKUP is defined this way VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)
In this case,
unique value = Lookup_value = Name
table you need to look at = Table_array = A2 to D7
return the column value you want = Col_index_num = C if you want “Company”, D if you want “Current Age”
Now the only optional value the formula needs is the “Range_lookup” – It can be either a true or false or blank
So follow these rules to fill in “Range_lookup”
- If the column which has your unique identifier is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true , or leave it blank.
- If the column which has your unique identifier is not sorted, or it’s sorted in descending order, then you must enter a value of false
You’re all set!
You need to fill in the following formulas to get the data:
For Company – =VLOOKUP(G2,A2:D7,3,FALSE)
For Current Age – =VLOOKUP(G2,A2:D7,4,FALSE)
For Experience in Years – =I2-VLOOKUP(G2,A2:D7,2)
Hope this helps!!!