“LOOKUP”
Used for – “LOOKUP” function in MS Excel
is used to looks up a value either from a one row or one column range or from
an array. Provided for backward compatibility. (Note :- It is strongly
recommended that one should use “VLOOKUP” or “HLOOKUP” instead of using LOOKUP)
Syntax – “LOOKUP” can be used in
two form – Vector Form and Array Form.
Syntax for Vector Form:-
LOOKUP(lookup_value, lookup_vector, [result_vector]).
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.
lookup_vector – 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.
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.
lookup_vector – 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.
result_vector – This is an optional field. This is also a row or a column
having same no. of cells as in lookup vector. In case the no. of cell are not
same then there are chances of wrong result display. To avoid inaccuracy of
result choose the result vector length same as of lookup vector.
Syntax for Array Form:-
LOOKUP(lookup_value, array)
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.
array – This is a table or you can say a group of rows and columns in which you want to search the value. It is compulsory field without which formula results in #N/A error.
array – This is a table or you can say a group of rows and columns in which you want to search the value. It is compulsory field without which formula results in #N/A error.
Example – Following example will clear
the LOOKUP function:-
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
|
1
|
APPLE
|
11
|
ALPHA
|
BETA
|
GAMMA
|
PI
|
|||
2
|
MANGO
|
13
|
1
|
2
|
3
|
4
|
|||
3
|
GRAPES
|
15
|
LOOKUPVALUE
|
VECTOR FORM
|
ARRAY FORM
|
||||
4
|
ORANGE
|
16
|
GRAPES
|
=LOOKUP(D4,A1:A5)
|
=LOOKUP(D4,A1:B5)
|
||||
5
|
PAPAYA
|
12
|
APPLE
|
=LOOKUP(D5,A1:A5,B1:B5)
|
|||||
6
|
BETA
|
=LOOKUP(D6,D1:G2)
|
|||||||
7
|
4
|
=LOOKUP(D7,D1:G2)
|
And
result you get as under:-
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
|
1
|
APPLE
|
11
|
ALPHA
|
BETA
|
GAMMA
|
PI
|
|||
2
|
MANGO
|
13
|
1
|
2
|
3
|
4
|
|||
3
|
GRAPES
|
15
|
LOOKUP VALUE
|
VECTOR FORM
|
ARRAY FORM
|
||||
4
|
ORANGE
|
16
|
GRAPES
|
GRAPES
|
15
|
||||
5
|
PAPAYA
|
12
|
APPLE
|
11
|
|||||
6
|
BETA
|
2
|
|||||||
7
|
4
|
#N/A
|
Here
Cell E4 & E5 are showing the result when LOOKUP function is used as vector
form and cell F4, F6 & F7 are showing results of array form of LOOKUP
function.
Note:- In case
of Array Form, you should
know that if the no. of rows are equal or more than column then MS Excel will
lookup value only in first column and shows the result from last column of the
array considering left most column as first column of the table and if no. of
rows are less than no. of column MS Excel will lookup the value in Top Row and
Shows the respective result from the last Row.
Also note that all the lookup
functions work from top to bottom and left to right for search Lookup_Value and
stops looking further after they get first value. So from this it is clear that
they are best suitable for unique values. Also Lookup will work on approximate
match of the lookup_value and stops on the most near value in the lookup row.
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.