Using INDEX MATCH across multiple sheets with a named range

Question

On Sheet A, I have the named range salaries2010. My goal is to reference a value from that range on Sheet B.

On Sheet A, this formula returns the correct value:

=INDEX(salaries2010,6,MATCH(C518,INDEX(salaries2010,1,0),0))

I know Sheet B able to reference the named range salaries2010 because this formula, in which I just point to the cell without using MATCH, returns the correct value:

=INDEX(salaries2010,6,1)

Alas, on Sheet B, my original formula doesn't work, even after I change the search term to C51 (where the same term resides)

=INDEX(salaries2010,6,MATCH(C51,INDEX(salaries2010,1,0),0))

I get the #NA! error.

Any ideas on what's wrong? Some idiosyncrasy of looking things up between sheets?

#N/A means the match was not found. #REF would indicate that the reference was bad. Check the data. — Scott Craner

onur rr onur rr · Accepted Answer · 2019-11-06T05:07:49

You can also Evaluate the formula(Formulas => evaluate formula on ribbon), and check where the N/A result returns.

Using INDEX MATCH across multiple sheets with a named range

1 Answers