Named range in Index & Match

Indirect should work..

Can you attach your sample workbook?

hi edward_glyver,
see i have just put the formulla in cell d9 =INDEX(INDIRECT("a1:a9",TRUE),MATCH(C2,A1:A9,0))
keeping the cell a1:a9 fill with some number, and it is working fine.

Hi,

The Paper Clip isn't working.

You can click on go advanced > Scroll a bit down & click on Manage Attachments > Chose File > Upload & then close the window > Post Reply.

How is your named range defined?

If it doesn't evaluate correctly for any reason then it will affect any formula that try to use it.

If it is defined correctly then you should be able to use =INDEX(INDIRECT(A1),etc... where the name of the named range to use is entered into A1.

edit:-

Look at E1 in your example, the cell is formatted to show "Year1" but the value in the cell is just "1" so INDIRECT(E1) is looking for a non existent range named "1"

Try this one,

=INDEX(INDIRECT(SUBSTITUTE(D19," ","")),MATCH(D20,D5:D16,0))

See edit to my previous reply for reason yours doesn't work correctly.

Just to add, you don't need so many functions for the task you're trying. While it might be more practical with other data sets, you could just format D19 and D20 in the same way as E4, then use

=INDEX(E5:G16,D20,D19)

No named ranges or indirect / match functions needed, just index in the simplest form.

But why indirect.. You can do it using Index & Match. Indirect is a Volatile function.

=INDEX(E5:G16,MATCH(D20,D5:D16,0),MATCH(D19,E4:G4,0))

Just change the input in cell D19 to just 1 instead of Year 1, or change column headers on the 4th row to match cell D19