Using (index,match

[j_Southern]

I have a named range "TABLE" and I want to be able to select an item by defining the row and column numbers.

This I do by using the formula INDEX(TABLE,2,MATCH(O3,H5:L5,-1))

Instead of spelling out the target range, I would like to use the range name in the Match part of the formula so something like
INDEX(TABLE,2,MATCH(O3,TABLE,1,-1)). This does not work but I hope shows what I am after.
The attached sheet might help.

Is this possible ? I hope so because I want to use the formula in a case where the range name and the lookup range will change, and my present formula will get very cumbersome.
John

[GC Excel]

Something like this?

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[jayajaya_4]

can I know y had u used (TABLE,1,) comma inside braces??

[GC Excel]

@jayajaya_4

Look at the INDEX function help. You can use INDEX function to return an array :
https://support.office.com/en-nz/art...a-f715f8b712dd

[j_Southern]

Thank you all for your valued advice. I have now been able to solve my problem
John