“VLOOKUP”
Used for – “VLOOKUP” function in MS Excel is
used to looks up a value only in first column starting from the left of the
selected table or array and returns a value in the same row from a column you
specify. Here you should know that Excel considered the table by default as in
ascending order.
Syntax – VLOOKUP(lookup_value,
table_array, col_index_num, [range_lookup]).
lookup_value – It is any text/number/formula/reference
cell, you typed or calculated which needs to be find in a row or column. It is
compulsory field without which formula results in #N/A error.
table_array – This is a vector or you can say a row or
a column in which you want to search the value. It is compulsory field without
which formula results in #N/A error.
col_index_num – This is a number of a column starting
from left, from which you want to display the value as a result. It is also a
compulsory field without which formula results in #VALUE! Error.
[range_lookup] – This is an optional field. It can be
used to tell MS Excel that lookup value should be exactly the same or it will
be approximate match the lookup value. Value in this position can be “TRUE”/1
for approximate match and “FALSE” / 0 / blank for exact match.
Example – Following example will clear the VLOOKUP function:-
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
|
1
|
Count
|
Alphabet
|
location
|
To search
|
2
|
1.9
|
|||
2
|
1
|
A
|
Delhi
|
Value in Col B approx. match
|
=VLOOKUP(F1,A1:C6,2)
|
=VLOOKUP(G1,A1:C6,2)
|
|||
3
|
1.9
|
B
|
Mumbai
|
Value in Col C approx. match
|
=VLOOKUP(F1,A1:C6,3,1)
|
=VLOOKUP(G1,A1:C6,3,1)
|
|||
4
|
2.1
|
C
|
Calcutta
|
Value in Col B exact match
|
=VLOOKUP(F1,A1:C6,2,”FALSE”)
|
=VLOOKUP(G1,A1:C6,2,”FALSE”)
|
|||
5
|
4
|
D
|
Bangalore
|
Value in Col C exact match
|
=VLOOKUP(F1,A1:C6,3,0)
|
=VLOOKUP(G1,A1:C6,3,0)
|
|||
6
|
5
|
E
|
Gurgaon
|
||||||
7
|
And
result you get as under:-
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
|
1
|
Count
|
Alphabet
|
location
|
To search
|
2
|
1.9
|
|||
2
|
1
|
A
|
Delhi
|
Value in Col B approx. match
|
B
|
B
|
|||
3
|
1.9
|
B
|
Mumbai
|
Value in Col C approx. match
|
Mumbai
|
Mumbai
|
|||
4
|
2.1
|
C
|
Calcutta
|
Value in Col B exact match
|
#N/A
|
B
|
|||
5
|
4
|
D
|
Bangalore
|
Value in Col C exact match
|
#N/A
|
Mumbai
|
|||
6
|
5
|
E
|
Gurgaon
|
||||||
7
|
Note:- To get the right result with VLOOKUP
you must remember following :-
1. First column must be in ascending order from
top to bottom.
2. It always works from top to bottom
3. It stops at the first value that matches
the lookup value in case there are duplicate values in the column.
4. Works only if the lookup value is within
the leftmost column of the selected array.
In case exact match you should have written “TRUE”
or 0 for range_lookup.
Dear readers, after reading the Content please ask for advice and to provide constructive feedback Please Write Relevant Comment with Polite Language.Your comments inspired me to continue blogging. Your opinion much more valuable to me. Thank you.