View Single Post
  #1   Report Post  
Lori H
 
Posts: n/a
Default specify range name in formula with concatenated string

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?