Errors when using named ranges

[Deina]

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),TRIM($C7))),Possessed,7,0)
it works finest kind, but when I use:
=VLOOKUP(LOWER(CONCATENATE(TRIM($A7),TRIM($B7),TRIM($C7))),Possessed,Have,0)
(input with "Use in Formula" button, to avoid spelling errors)
I get a #REF error.

 

[Jacob Skaria]

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,range_lookup)

Since your array range starts from Column A the below would work
=VLOOKUP(LOWER(CONCATENATE(TRIM($A7),TRIM($B7),TRIM($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