Index Match

[brandybartz]

I am trying to get the below formula to work in place of a vlookup that I was using.. I followed a tutorial but it is not working (giving me an NA instead of the value)

=INDEX('Date View-All'!$A$1:$AA$160,MATCH('1-5 Set'!$B11,'Date View-All'!$C$1:$AA$160,0),MATCH('1-5 Set'!$BA$3,'Date View-All'!$A$6:$AA$6,0))

Tutorial Example..

So, you start by writing two MATCH functions that will return the row and column numbers for your INDEX function.

• Vertical match - you search through column B, more precisely in cells B2 to B11, for the value in cell H2 ("USA"), and the corresponding MATCH function is this: <code>=MATCH($H$2,$B$1:$B$11,0)</code> This MATCH formula returns 4 because "USA" is the 4^th item in column B (including the column header).
• Horizontal match - you search for the value in cell H3 ("2015") in row 1, i.e. in cells A1 to E1: <code>=MATCH($H$3,$A$1:$E$1,0)</code> This MATCH formula returns "5" because "2015" is the 5^th column.

Now, put the above formulas inside the INDEX function, and voila:
<code>=INDEX($A$1:$E$11, MATCH($H$2,$B$1:$B$11,0), MATCH($H$3,$A$1:$E$1,0))</code>
If you replace the MATCH functions with the returned numbers, the formula is much easier to understand: <code>=INDEX($A$1:$E$11, 4, 5, 0))</code>
Meaning, it returns a value at the intersection of the 4^th row and 5^th column in range A1:E11, which is the value in cell E4. Easy? Yep! : )

 

[63falcondude]

I believe that your problem is here, highlighted in red.

=INDEX('Date View-All'!$A$1:$AA$160,MATCH('1-5 Set'!$B11,'Date View-All'!$C$1:$AA$160,0),MATCH('1-5 Set'!$BA$3,'Date View-All'!$A$6:$AA$6,0))

MATCH is not made for multiple columns and multiple rows. Perhaps this should be $C$1:$C$160? Hard to tell without a sample of your data.

 

[James006]

Hi,

Most probably ... your first match() function is looking into the C Column ... :wink:

=INDEX('Date View-All'!$A$1:$AA$160,MATCH('1-5 Set'!$B11,'Date View-All'!$C$1:$C$160,0),MATCH('1-5 Set'!$BA3,'Date View-All'!$A$6:$AA$6,0))

HTH

 

[brandybartz]

Thank you so much.. That was the problem..

 

[63falcondude]

Thank you so much.. That was the problem..

Glad to help.