Vlookup with named range

**realniceguy5000** · 08-23-2011, 09:02 AM

Hi, I have named a range on another worksheet Same workbook "lrow" I have placed this formula in the named range:

='UPC SCANS'!$B$2:INDEX('UPC SCANS'!$B2:$D$1000,COUNTA('UPC SCANS'!$B$2:$D$1000),1)

I placed this formula above in a cell to see what it would return and it returns 0 ? I thought it was suppose to return the lastrow of data in col B

On another worksheet I have a vlookup formula like this which works.

=VLOOKUP(A6,'UPC SCANS'!B2:D1000,3,FALSE)

However when I try to add the named range in place of the B2:D1000 I Get a ref# error?

Here is what I have for the formula:

=VLOOKUP(A6,'MASTER(REV1).xls'!lrow,3,FALSE)

Anyone have any idea's what is wrong. Maybe something is wrong with the named range formula ?

Anyway Thanks for looking, Mike

**Bob Phillips** · 08-23-2011, 09:26 AM

How about

=VLOOKUP(A6,OFFSET('UPC SCANS'!$B$2,0,0,COUNTA('UPC SCANS'!$B:$B),3),3,FALSE)

**NBVC** · 08-23-2011, 09:34 AM

The COUNTA() part of your named range should be 1 column wide.

e.g

='UPC SCANS'!$B$2:INDEX('UPC SCANS'!$B2:$D$1000,COUNTA('UPC SCANS'!$B$2:$B$1000),1)

and doesn't this work after the above change? i.e. do you need the workbook reference?

=VLOOKUP(A6,lrow,3,FALSE)

**realniceguy5000** · 08-23-2011, 12:11 PM

Hi, Thank You

Sorry for the delay...

But Still getting a ref error. I'm not sure what is wrong.I dont need a workbook ref as you said, I removed that, Not sure what to do now I been messing with all morning.

I tried to included an example workbook, But keep getting database error from this site.

Thank You, Mike

**NBVC** · 08-23-2011, 12:13 PM

Sorry try this one instead as the named range:

='UPC SCANS'!$B$2:INDEX('UPC SCANS'!$B2:$D$1000,COUNTA('UPC SCANS'!$B$2:$B$1000),4)

If still no go, try zipping the file first before uploading.

**realniceguy5000** · 08-23-2011, 12:45 PM

Ok, Here is the workbook I'm working with, let me know if you have any questions.

Thanks again for your help.

Mike

**NBVC** · 08-23-2011, 01:17 PM

Ok, I can't count

It should've been 3 not 4 at the end..

='UPC SCANS'!$B$2:INDEX('UPC SCANS'!$B$2:$D$1000,COUNTA('UPC SCANS'!$B$2:$B$1000),3)

**realniceguy5000** · 08-23-2011, 02:26 PM

Thanks, it appears to be working now,

With all the changes I tried with that formula, I wonder why I never tried a 3 is beyond me.

In my mind it was a 1

Anyway I hope that my formula troubles are not wearing off on you... lol

Thanks Again...Mike

**NBVC** · 08-23-2011, 02:36 PM

It is 3 because you are indexing this range: 'UPC SCANS'!$B$2:$D$1000

and in you want to go from column B to column D which is 3 columns away... i.e. the range you want to name is 3 columns wide.