Using Concatenate inside formula to refer to a named range

[gmbristow91]

Hi,

I have a formula that refers to a named range to return the value, but the value it returns depends on what is chosen from a dropdown list. The value from the dropdown list plus a text string would = the named range to reference. The formula I am currently using is throwing the #value ! error. This is what I have so far.

=INDEX(CONCATENATE(B33,"_Schemes"),MATCH($B$32,CONCATENATE(B33,"_Schemes2"),0)+ROWS(E28:E$28),COLUMNS($D28:E28))

 

[Scott Huish]

Put an INDIRECT around your CONCATENATE pieces, however you can drop the CONCATENATE and just use & for those types of things:

=INDEX(INDIRECT(B33&"_Schemes"),MATCH($B$32,INDIRECT(B33&"_Schemes2"),0)+ROWS(E28:E$28),COLUMNS($D28:E28))

 

[gmbristow91]

Thank you very much! That worked perfectly!