Excel is vast, so does its functionalities. ”Name it and you have it” said an unknown technocrat when few of his fresh bees questioned him “why excel”?
Today we are going to discuss one of the very useful functionality of EXCEL which makes analysis of huge data go like a cake walk.
Let’s assume we have data of a logistics company having around 1000 of records including the pin code, state, city and the courier services used in those cities. One fine day my manager comes and asks me,”hey buddy, I have a list of pin codes, and can you please let me know how many courier companies deliver to code 411001? And yeah please be quick I am on a call with client he wants to know ASAP…”
Now out of this ocean of data how can you search for how many courier companies deliver to code 411001 that too within few seconds?
This is where concept of VLOOKUP AND HLOOKUP comes up:
HLOOKUP: when values which need to be compared are located in a row across the top of a table of data, and what we want to look is a specified number of rows.
VLOOKUP: when values need to be compared are located in a column to the left of the data you want to find.
VLOOKUP /Vertical Lookup/Row Wise Lookup
Let’s consider the same example which I have mentioned above regarding the logistics company and the manager requirements.
This has my sheet which has all data of the logistics company and my manager sheet which is having pin codes against which we need to find certain data.
Task: No.of courier services against pin code 410403?
Let’s get it!!!!!!!!!!!!!
Firstly, how does lookup like?
0 means exact match / 1 means approximate match.
Secondly, Steps to use VLOOKUP
“BE QUICK AND SMART AND WITHIN FEW SECONDS LET YOUR MANAGER/CLIENT KNOW WHAT HE WANTS”!!!
What we did till now was searching data vertically what if we want to search with respect to the columns.
Let’s have a look …
Now if we have to find that 410103 is the pin code for which state, let’s get it by hlookup
=HLOOKUP (“State”, MYSHEET! A1:C147, 3, 0)
When we talk about syntax, same as lookup with a slight difference, here
First argument: what you want to look for however column wise
Second argument: where are you looking?
Third argument: select the data
Fourth argument: which column has the data you want to return?
Last argument: you want exact match or approximate?
Note: Follow same steps as Vlookup to create the formula
Here it will return the o/p as follows