specify range name in formula with concatenated string

[Guest]

Can I refer to a named range with a "built" reference?

Example: If FORM!B:B is named "darkred", the folowing formula in cell D1 in
a separate worksheet (same workbook) will return the value in the "darkred"
column for the row with the matching column A value.
VLOOKUP(A1, FORM!, 2, false)
I have additional values in the second worksheet columns B (rows = red,
green, blue) & C (rows = dark, medium, light). So that a concatenated string
(C1 & B1)can represent the named column. I want to nest that into the
VLOOKUP formula to specify the return value column...although VLOOKUP uses
column index number (which in this example is the column number), so the
formula gets messy:
COLUMN(C1 & B1). ...AND It doesn't work.
Is there a way to use the string (C1 & B1) to refer to the "darkred" column?

 

[KL]

Hi Lori,

Try this formula:

=VLOOKUP(A1,INDIRECT(C1&B1),2,0)

Regards,
KL

 

[Guest]

Thank you for trying, but this doesn't work.
I need the named column to be the VLOOKUP column index (the location of the
data to be returned, where you have the number 2), because it will vary for
each row of my worksheet, and I want to copy a single formula throughout the
worksheet column so added items calculate properly without adjusting the
formula. You have the indirect function in the VLOOKUP table array field, so
the formula is looking for the lookup text in the named column. I have tried
the INDIRECT function in the column index field, but it doesn't work.

I have since solved this problem by adding a row (row 1) to the lookup array
that contains the column names, and using the MATCH function to refer to the
column where row 1 matches the specified string rather than using named
ranges to refer to the correct column:
=VLOOKUP($A2,'FORM'!,MATCH('FORM'!$B$1:$N$1,0),FALSE)