LOOKUP

No Comments

“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.
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.

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.