Index return value REF#!

[Pilar Morales]

This formula returns REF#!

=1*(INDEX(x!$A$1:$AW$128,MATCH($A$27,x!$A$2:A500),MATCH(N7,x!$B$1:$AV$1)))

The values are correct in the evaluate formula box, Row:10, Col 2, but then
it breaks down.

I'e also tried
INDEX(x!$A$1:$AW$128,MATCH($A$27,x!$A$2:A500),MATCH(N7,x!$B$1:$AV$1))

I need the formula to return the value in that cell (in this case, B10)

What am I doing wrong?

Thank you!

 

[Shane Devenshire]

Hi,

Match only works against a single column or single row. However, that would
not return a REF error. The probalby is occuring because you don't have a
sheet in the current book named X.

 

[Pete_UK]

Your first MATCH is against a range A2:A500 (suppose it returns 350),
but your INDEX table only covers rows 1 to 128, so the match row is
outside the range of the table and therefore the formula returns an
error (#REF).

Adjust your ranges so you do not go outside the size of the table.

Hope this helps.

Pete