PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

EXCEL FUNCTIONS-LOOKUP

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

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

=F(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

 

=F(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

 

EXCEL LOOKUP FUNCTION ANIMATED VECTOR
EXCEL:MATCH FUNCTION:ANIMATED EXAMPLE

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.

 

 

EXCEL LOOKUP FUNCTION ARRAY FORM
EXCEL:LOOKUP FUNCTION:ANIMATED EXAMPLE: ARRAY FORM

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.

 

DO YOU KNOW

LOOKUP FUNCTION SHOULD BE ONLY USED WHEN PARTICULARLY NEEDED E.G. IF WE DON'T WANT TO SPECIFY HORIZONTAL OR VERTICAL LOOKUP. USE VLOOKUP AND HLOOKUP IN PLACE OF LOOKUP.

SUGGESTIONS

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: