Errors when using named ranges

D

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.
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top