How To Use INDEX and MATCH In A Table With INDIRECT In Excel

In this tutorial we’ll look at the traditional data range lookup using the INDEX and MATCH functions, but add a twist to look up the values in a table, plus a bonus twist using the INDIRECT function.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a data range with salespeople and sales numbers for 12 products:

71615-1

Below that I want to pull the sales numbers for a specific salesperson from a Data Validation dropdown list, and copy it down for each product:

71615-2

I’m going to use the traditional INDEX/MATCH function combination. You can learn more about these functions on my blog post located here. I’ll define the range for my INDEX portion of my formula with just the sales data from my data range, and use the MATCH function to define the row and column intersection. The formula is:

=INDEX($B$2:$M$7,MATCH($B$11,$A$2:$A$7,0),MATCH(A12,$B$1:$M$1,0))

71615-3

Notice that I locked all the cell references using the F4 key to make them absolute cell references, except the one reference to the product in column A, so that will change as I copy it down.

Now, on the next tab, I have the same data, but in a table named SalesData. I can construct the same formula as with a data range, but notice that I don’t need to convert the references to the table to absolute, since they are already defined based on the columns of the table. The formula is:

=INDEX(SalesData[[Prod1]:[Prod12]],MATCH($B$11,SalesData[Name],0),MATCH(A12,SalesData[[#Headers],[Prod1]:[Prod12]],0))

71615-4

Now, one last twist. Instead of using the MATCH function to define the column, let’s instead pre-define the range for the INDEX function using the INDIRECT function, and utilize the product names in column A to reference the appropriate column from our Table. That formula will be:

=INDEX(INDIRECT(“SalesData[“&A12&”]”),MATCH($B$11,SalesData[Name],0))

71615-5

For those not familiar with the INDIRECT function, it returns the reference specified by a text string. So in our formula, the INDIRECT portion is:

INDIRECT(“SalesData[“&A12&”]”)

What we’ve done is concatenate “SalesData[” with the Product name (in this case, cell A12 is “Prod1”), and then concatenated that with a closed bracket “]”. So the result of this INDIRECT function is SalesData[Prod1] which is the name of the first data column in our SalesData table.

One note of caution: the INDIRECT function is a VOLATILE function, which means it will recalculate every time the worksheet recalculates. For small data sets, this is not an issue, but with large data, that can slow your process down, so be careful when using any volatile function.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , ,

4 Responses to How To Use INDEX and MATCH In A Table With INDIRECT In Excel

  1. Martrica February 28, 2016 at 3:38 pm #

    I am trying to do something similar. Using your example is there a way to index match by the name and return back concatenate of Prod & Amount? In my results column I would like it read as Prod1 – 3036.

    • Michael Rempel February 28, 2016 at 5:47 pm #

      Martrica – yes you can quite simply. Instead of the formula in call C12 of the Tables worksheet being:

      =INDEX(INDIRECT(“SalesData[“&A12&”]”),MATCH($B$11,SalesData[Name],0))

      Change it to:

      =A12&” – “&INDEX(INDIRECT(“SalesData[“&A12&”]”),MATCH($B$11,SalesData[Name],0))

      This will concatenate the product name in column A with the values that the INDEX formula pulls

  2. Carms March 1, 2017 at 8:38 pm #

    Can I use IF/INDEX/MATCH to find cross of amount/item in a full sheet that contains not only amounts but categories (information shifts too much depending on available data to use vlookup/hlookup) and return 0 if that info is not available?

    Tried the following:

    =IFERROR(INDEX(pos!A1:AT81,MATCH(Wkly!B60,pos!A50:V78,0),MATCH(Wkly!B57,pos!A50:V78,0)),0)
    Wkly is tab one where i want values to show up/ pos is tab where i would like to extract value from

    =IF(ISERROR(MATCH($B$57&B59,pos!$A$4:$U$72,0)),0,INDEX(pos!$A$4:$U$72,MATCH($B$57&B59,pos!$A$4:$U$72,0)))

    =IFERROR(INDEX(pos!$A$4:$U$72,MATCH(Wkly!B66,pos!$A$4:$U$72,0),MATCH(Wkly!$B$57,pos!$A$4:$U$72,0)),0)

    nothing works

Leave a Reply