Hi Lori,

Try this formula:

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

Regards,
KL


"Lori H" wrote in message
...
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?