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),MATC H(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!

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Pilar Morales" wrote:

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),MATC H(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!

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

On Jan 28, 5:55*pm, Pilar Morales
wrote:
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),MATC H(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!