I want the attached spreadsheet to do the following.
A selection is made in cell B2 of the "assumptions" tab. Each of the available selections has a named Array.
In the tab "Q's etc", column B, starting at row 5, should look up the value from the selected Named Array.
The value from the Named Array should be the value in column B that matches Column A.
Cell H14 on "Q's etc" is my attempt. It is returning "REF!"
I hope I explained this well enough.
Nothing attached mate - you need to press upload
I hope this upload worked.
It's not clear to me what result you want. INDEX takes three arguments (at most): a range, a row number and a column number. Since your named ranges are only one column, you can't use 25+A10 as a column number - only 1 would be valid.
I want to populate cells B5 through B125 on tab "Q's etc" The values should come from the named array selected in cell B2 on the "assumptions" tab. The named arrays in the selection options are 2 column arrays.
Do you mean like this?
=INDEX(OFFSET(INDIRECT($H$10),,1),MATCH(TEXT($A10,"@"),INDIRECT($H$10),0))
Only tested on Excel 365
Also appears to work in Excel 2013
Thank you Purfleet. This works!
