Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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),FAL SE) "KL" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count number of days in range which are less than today | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
If formula for date range | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
HOW TO USE A FORMULA TO CHANGE CELL COLOR ACCORDING TO DATE RANGE. | Excel Worksheet Functions |