LOOKUP FUNCTION comes under the LOOKUP AND REFERENCE group of functions in Excel.
LOOKUP FUNCTION facilitates us to find a value in a row or column and retrieve the value from other row or collumn at the same position.
LOOKUP FUNCTION is similar to VLOOKUP AND HLOOKUP functions. But HLOOKUP AND VLOOKUP functions are more advanced than LOOKUP FUNCTION.
Lookup function works both in horizontal and vertical directions whereas HLOOKUP and VLOOKUP are specifically designed for horizontal and vertical lookup.Always go for HLOOKUP OR VLOOKUP instead of LOOKUP FUNCTION.Its given here for information purpose and maybe in any case this function can be used.
PURPOSE OF LOOKUP IN EXCEL
LOOKUP FUNCTION finds a value in a row or column and retrieve the value from other row or column at the same position from the result array.
PREREQUISITES TO LEARN lookup
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Easier to understand if user already knows how to use VLOOKUP.
- Basic understanding of rows and columns in Excel.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above What Excel does? How to use formula in Excel?
SYNTAX: LOOKUP FUNCTION
The syntax ( the way how formula is phrased for excel) of LOOKUP FUNCTION are as follows:
The LOOKUP FUNCTION supports two different formats of the function.
VECTOR FORMAT
=LOOKUP (VALUE TO BE FOUND , LOOKUP RANGE , RESULT RANGE)
VALUE TO BE FOUND The value to be found and matched
LOOKUP VECTOR The range of cells, where we will try to find the match. ONLY ONE ROW OR ONE COLUMN.
RESULT VECTOR The range of cells, which will be returned as result. The value which is corresponding to the lookup_value will be returned as result. The Result table needs to be equal to the Lookup table.ONLY ONE ROW OR ONE COLUMN.
ARRAY FORMAT
=LOOKUP (VALUE TO BE FOUND , LOOKUP RANGE )
VALUE TO BE FOUND The value to be found and matched
LOOKUP ARRAY The range of cells, where we will try to find the match. ONLY ONE ROW OR ONE COLUMN.
EXAMPLE 1:LOOKUP IN EXCEL [VECTOR FORMAT]
DATA SAMPLE
Suppose we have a data where we have different number of people in the club on the weekdays. And we need to find out the number of people presence on any particular day.
DAY NO. OF PEOPLE
1 SUNDAY 12
2 MONDAY 32
3 TUESDAY 14
4 WEDNESDAY 54
5 THURSDAY 65
6 FRIDAY 12
7 SATURDAY 76
STEPS TO APPLY LOOKUP FUNCTION-VECTOR FORM
The example shows the number of people visits in club. The ID, DAY and no. of people are given in a table.
In first case
We are trying to find out the no. of people visits on TUESDAY. The formula used is
=LOOKUP(“TUESDAY”,H7:H19,I7:I19)
The first parameter is TUESDAY put in “” as it is a text.
Second parameter is the range from which the lookup value is to be found.
Third parameter is the range from which the value is to be returned, or simply saying the value which we have are searching for.
The output is 14 as evident from the table.
Second case:
We use the id to be found by putting the address of the cell as G15. The lookup range and result range is same as in earlier case.
The formula used is
=LOOKUP(G15,G7:G19,I7:I19)
The result is as expected as 65.
In third case, we tried to find a ID which is not present in our table.
Let us try to find 8 in IDs.
The formula used is
=LOOKUP(8,G7:G19,I7:I19)
The function will try to find 8 but as we can see the value is not present. So the function will settle
finding the nearest lower which is 7 and it’ll return the no. of visit of day id 7.
The answer is 76
EXAMPLE 2:LOOKUP IN EXCEL[ARRAY FORMAT]
DATA SAMPLE
The formula for the array form is=lookup(lookup value, lookup range)Let us take the same example and try using the ARRAY FORMAT on the same example.
The two cases has been discussed.
We put the formula in K32
=lookup(g31,g29:g41)
It tries to find out the value 2, in the full range from g29:g41.
In second case we try to find out a value in some other array and the function return #N/A value not available error.
