Using a cell reference to a named range in the index formula - how?

[jsgeare]

Here is the index/match formula:

=INDEX(Zone5,MATCH(C2,NextPound,0),1)

Zone5 is a range of values. As long as it is explicitly written out as Zone5, the formula works. But if a reference to a cell containing Zone5 is used, the formula returns a #REF error. Doesn't matter if the value in the cell is entered as text or calculated. Thus, if the formula becomes:

=INDEX(B2,MATCH(C2,NextPound,0),1) where B2 either has Zone5 as text, or as a calculated value, then the formula fails.

Is this just the way it is? Or is there some trick to getting index to recognize the range?

Using XL 2013- latest version.

Thanks in advance.

 

[Momentman]

If B2 contains the text B5, then you might need

=INDEX(INDIRECT(B2),MATCH(C2,NextPound,0),1)

 

[jsgeare]

BINGO! Thanks.