Index return value REF#!

P

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!
 
S

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.
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top