INDEX, MATCH, MATCH returning wrong value

[richlud]

I am using the following formula to return values from a different worksheet but its pulling in the value 2 rows down and 2 columns across from what it should be returning.

=IFERROR(INDEX('Numbers input'!$C$3:$Q$300,MATCH($B7,'Numbers input'!$B:$B,0),MATCH(D$5,'Numbers input'!$3:$3,0)),0)

Probably something really simple, but can't work it out! Can anybody help?

 

[steve the fish]

Hi. Your match formula needs to be looking in the same rows/columns as your index range so something like:

=IFERROR(INDEX('Numbers input'!$C$3:$Q$300,MATCH($B7,'Numbers input'!$B$3:$B$300,0),MATCH(D$5,'Numbers input'!$C$3:$Q$3,0)),0)

Note the first match starts at row 3 and the 2nd match starts at column C.

 

[AlanY]

because your index start on C3, i.e. always 2 behind the match().

simple fix

=IFERROR(INDEX('Numbers input'!$C$3:$Q$300,MATCH($B7,'Numbers input'!$B:$B,0)+2,MATCH(D$5,'Numbers input'!$3:$3,0)+2),0)

 

[richlud]

Perfect, thanks both!