03-09-2021
Introduces Excel Without Mouse
Sap Cubix
By Sandeep Arora
(Corporate Excel Trainer)
First Time In
India
Let’s get Started
Class :- 8
1
03-09-2021
Content :-
Vlookup
Pre requisite of VLookup
If Error VLookup
Vlookup Array
Vlookup with Approximate
Match
Hlookup
1. VLookup
V Lookup means vertical lookup which is used to retrieve the data from a range of cells based on Column No.
The Formula of V Lookup is:-
=Vlookup (Lookup Value, Table Array, Column No, True/False)
Lookup Value means:- What you want to lookup.
Table Array:- Means the Range that you want to look for it.
Column No:- Means Range containing the value in return
True/False:- Means 1 for Approximate & 0 for exact Match
2
03-09-2021
2. Pre Requisite of V Lookup
Data should be Unique, means there is no duplicate data in the range
Range goes from Left to Right not Right to Left
Data should be in proper format
Lookup value must be from the first Column in the data that are common in both the data
If there is a duplicate data, then v lookup always gives you the first result.- Drawback
There should be common field between both the data
D2 Means Lookup Value
Sheet1!B:D means it’s a range
3 means the Column No
0 is for exact Match
Result
3
03-09-2021
3. Iferror V lookup
Vlookup with Iferror function helps the user return error message based on the user choice if any situation
occurs.
=Iferror(Vlookup(Lookup Value, Table Array, Column No, True/False)
4
03-09-2021
4. Vlookup Array
Vlookup normally takes only one value, but since we are using an array formula, we can give it multiple values
by putting them in brackets & then Control & Shift & Enter.
Active
Cell is B2
Select the
Range for
the
vlookup
function
After Applying the formula, Press Control & Shift & Enter
5
03-09-2021
Vlookup in One Go, No Need to apply formula again & again- Benefit of Vlookup Array
5. Vlookup With Approximate Match
When you Select Approximate match, you are not asking Excel to Match Values that are approximately the
same as each other.
In this, we have to enter 1 or false for the approximate match.
6
03-09-2021
Result
6. HLookup
H Lookup means Horizontal lookup which is used to retrieve the data from a range of cells based on Column No.
The Formula of H Lookup is:-
=Hlookup (Lookup Value, Table Array, Column No, True/False)
Lookup Value means:- What you want to lookup.
Table Array:- Means the Range that you want to look for it.
Column No:- Means Range containing the value in return
True/False:- Means 1 for Approximate & 0 for exact Match
7
03-09-2021
Data is horizontal, Rest
all Same
We can apply Hlookup
array in this also
Result
8
03-09-2021
Sap Cubix
