OK, I think I know what you are doing now.

You cannot use a union to create the Validation list - you need to use formulas to put the two lists
together. With Range1 and Range2 being your named ranges, enter this and copy down until you get ""
values:

=IF(ROW(A1)<=COUNTA(Range1),INDEX(Range1,ROW(A1)), IF(ROW(A1)<=(COUNTA(Range1)+COUNTA(Range2)),INDEX( Range2,ROW(A1)-COUNTA(Range1)),""))

Then use that list for your data validation.

You could also use the above technique to pull the second column into the table to use for the
VLOOKUP, or you could use this - if the first range doesn't have what you want (you get the NA
error) then the second range should. If neither range has the value, then you will get an error:

=IF(ISERROR(VLOOKUP(Value,Range1,2,False)), VLOOKUP(Value,Range2,2,False),
VLOOKUP(Value,Range1,2,False))


HTH,
Bernie
MS Excel MVP


"RD Wirr" wrote in message
...
Hi Bernie,

Thanks for the input but I can't make this work. I only get an #NA.

Maybe I should explain better.
I have two ranges that look something like this:
Range1
A B
1 1 a
2 2 b
3 3 c

Range2
E F
10 5 e
11 6 f

I need to do two things. I need to get a complete listing of the data in the
ranges in A1:A3 and in E10:E11 to use as a validation list. I also need to
access the larger ranges A1:B3 and in E10:F11 to do a lookup on the data in
B1:B3 and F10:F11 using the criteria from the validation list.

I think I mentioned, the actual ranges I am using are dynamic.

Can you give me another hint?

Regards,
RDW


"Bernie Deitrick" wrote:

RDW,

No need to join the ranges. Instead of VLOOKUP, try using

=INDEX(RANGE2,MATCH(Value, RANGE1,FALSE))

HTH,
Bernie
MS Excel MVP


"RD Wirr" wrote in message
...
I have some dynamic named ranges, the contents of which, I need to
concatenate to create a new range. Then I need to do a vlookup on the
resulting range. I also need to use the new range in a validation dropdown
list. The original ranges are defined using something like
=OFFSET($A$1,0,0,COUNTA($A:$A),2). I can join two ranges together in the
'Define Name' box referring to =Range1,Range2 and excel seems to recognize
the two non-contiguous ranges but the range doesn't work in a formula or
list. The two original ranges are the same width but dynamic length.

Thanks in advance,
RDW