Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Errors when using named ranges
I'm attempting to use VLookup() to access data in the 7th column of a
different sheet in the same workbook. I have named the range that I want as "Have", with a range of ='Owned Hair'!$G$7:$G$153 The range "Possessed" ='Owned Hair'!$A$6:$I$153 (Row 6 is column headers) If I use the following formula: =VLOOKUP(LOWER(CONCATENATE(TRIM($A7),TRIM($B7),TRI M($C7))),Possessed,7,0) it works finest kind, but when I use: =VLOOKUP(LOWER(CONCATENATE(TRIM($A7),TRIM($B7),TRI M($C7))),Possessed,Have,0) (input with "Use in Formula" button, to avoid spelling errors) I get a #REF error. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Errors when using named ranges
Hi Deina
If you review the syntax below for VLOOKUP(), the 3rd argument is column number..but you are passing a range. =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) Since your array range starts from Column A the below would work. =VLOOKUP(LOWER(CONCATENATE(TRIM($A7),TRIM($B7),TRI M($C7))),Possessed,COLUMN(Have),0) But a general workaround for this would be =VLOOKUP(lookup_value,Possessed,COLUMN(Have)-COLUMN(Possessed)+1,0) If this post helps click Yes --------------- Jacob Skaria "Deina" wrote: I'm attempting to use VLookup() to access data in the 7th column of a different sheet in the same workbook. I have named the range that I want as "Have", with a range of ='Owned Hair'!$G$7:$G$153 The range "Possessed" ='Owned Hair'!$A$6:$I$153 (Row 6 is column headers) If I use the following formula: =VLOOKUP(LOWER(CONCATENATE(TRIM($A7),TRIM($B7),TRI M($C7))),Possessed,7,0) it works finest kind, but when I use: =VLOOKUP(LOWER(CONCATENATE(TRIM($A7),TRIM($B7),TRI M($C7))),Possessed,Have,0) (input with "Use in Formula" button, to avoid spelling errors) I get a #REF error. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating ranges in closed workbooks without #REF errors | Excel Discussion (Misc queries) | |||
Ignore errors when calculation average of multiple ranges | Excel Worksheet Functions | |||
3D Named Ranges | Excel Worksheet Functions | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Named Ranges | Excel Worksheet Functions |