Lookup in Excel using INDIRECT Function – Instead of VLOOKUP

April 19th, 2011 by Tips Master | Posted under Excel Functions.

INDIRECT FunctionAs promised in the previous post i will be continuing with the tutorial on doing Lookup using INDIRECT, MATCH and ADDRESS functions. We will see in this post how to do the Vertical Lookup using the INDIRECT function in Excel.  This can be used instead of  VLOOKUP function in Excel. Check the previous post for the tutorial on doing Horizontal Lookup using INDIRECT Function.

 

 

Vertical Lookup using the INDIRECT Function

See the Example below.

Vertical lookup Example

 

 

Here we have Months in the Row and we need to get the data in Cell E3 against the value entered in cell D3. The following formula is used to Lookup the value.

Vertical lookup Solution in Excel

I have used the following formula in the Cell E3=INDIRECT(ADDRESS(MATCH($D$3,$A$2:$A$14,0)+1,2)).  See the snapshot above.

Know the Function in detail

As seen in our earlier post on Horizontal Lookup using Indirect Function . We will split the formula in to little pieces to know how it is working. It’s always better to learn Excel formulas in this way and this is the  way i normally prefer whenever i come across some big and complex Excel function.

MATCH Function


The match Function will look in cell D3 and will check the value in Cell D3 in the range A2 to A14. This will return the value 7 as the month May appears in the 7th Row. This value will be used inside the Address function to convert it into a reference. There is an additional 1 added because the range selected starts only from A1 which will retrieve the Value 6 and 1 is added to get the 7th Row. if we include our range from Cell A1 we can avoid adding this number in the match function.

Vertical lookup Solution using MATCH Function

ADDRESS Function

Once the match function retrieves the value 7, the Address function can be simplified as below

Vertical lookup Solution using ADDRESS Function

The Address function =ADDRESS(7,2) will show the reference as $B$7. We have replaced the match function with the values to understand it better.

INDIRECT Function

We further simplify our example with the values retrieved from ADDRESS function. See the Snap below

Vertical lookup Solution using INDIRECT Function

As you have seen in the snapshot Indirect function is further simplified as INDIRECT(“$B$7″) which will Lookup the value from the cell B7.
This is the benefit of using ADDRESS function inside Indirect function rather than directly using the reference inside the INDIRECT function.

Lookup data from another Worksheet

If you need to lookup from a different sheet then you need to add “Sheet name” to the Address function.

Vertical lookup from another work sheet

The data is retreived from the another worksheet named “Data”

Lookup data from another workbook

To get the data from a different workbook then add the workbook name before the sheet name within Square brackets in the Address Function
In the earlier case let’s say we need to get the data from another workbook named Book2 and sheet named Data . So the formula will be =INDIRECT(ADDRESS(MATCH($D$20,$A$2:$A$14,0)+1,2,1,1,”[Book2]Data”))

 

Download the Excel File

Also see the tutorial on data consolidation from multiple tabs using Indirect Function.

Hope you enjoyed the tutorial. Will be back with the rest of the tutorial soon.

Comments

4 Responses to “Lookup in Excel using INDIRECT Function – Instead of VLOOKUP”
  1. Zafar Iqbal says:

    Files downloading from this web site become zip files. Just rename its extension from zip to xlsx. It will work well.

  2. hiran says:

    Hi friend..
    Please help me to solve this problem…..
    I have an excel work book with three work sheets,

    above sheet contains all the sales details (i.e. Colombo(city or region)) for each month and the names of each sales persons, there is another sheet like this as collections in the same format to record collected cash amounts

    Summary sheet the column header is a drop down list which user can select either sales or collection.And also there is a another two drop down list for select to year and month.

    What I need to happen is when user selects sales or collection,year and month from the drop down appropriate data should be displayed against each sales person’s.

Do you have any comments on Lookup in Excel using INDIRECT Function – Instead of VLOOKUP ?