Hi Bernie,

Thanks for the input but I cant 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