Vlookup on Non Contiguous Named Range

**sflemings** · 01-20-2010, 05:20 AM

Hi everyone,

I'm trying to do a vlookup on a non contiguous named range.

I have a non contiguous named Range called "temp".

Please Login or Register  to view this content.

I'm trying to perform a vlookup using this range by I'm getting an #N/A error

Please Login or Register  to view this content.

Any ideas how this works?
Thanks.

**Richard Buttrey** · 01-20-2010, 05:37 AM

Hi,

Why not use the contiguous range A1:B7?

HTH

**sflemings** · 01-20-2010, 05:45 AM

I've considered that but I don't want to do it that way. The reason I'm asking is that I have a range of information in A1:B150 and a second range of information in D1:E150. I don't want to have just one big list A1:B300.

**Richard Buttrey** · 01-20-2010, 06:03 AM

Hi,

So are you saying your non contiguous ranges that you're trying to use are in both different rows and different columns. Your original post only suggested they were in different rows which was the question I was answering.

If you're wanting to look in either A or D why not use an If test incorporated with two vlookups. e.g. IF(lookup in A:B=true then lookup A:B, otherwise lookup D:E)

Rgds

**sflemings** · 01-20-2010, 06:44 AM

Hi Richard,
My apologies. Yes, my original post contained everything in columns A and B and in different rows. I was using that as an example! I should have said columns AB and DE.
I was just checking to see if someone knew an easy way to perform a vlookup on multiple ranges. I have tried your suggestion and it works correctly. Thanks.
Ps. Performing a SUM(temp) actually works fine and handles correctly. There's something with the vlookup that is causing the problem.

**DonkeyOte** · 01-20-2010, 06:54 AM

The table_array in a VLOOKUP must be contiguous.

SUM can work with multiple values, areas etc - eg

Please Login or Register  to view this content.

VLOOKUP can not

Please Login or Register  to view this content.

The fact that A1:B10,D1:E10 may be a Named range does not alter anything - the Name still evaluates to a non-contiguous range and is thus not viable for use as table_array in a VLOOKUP.