HI Domenic -
unfortunately i still can't get this working, even with my modifications. I
don't receive an error msg just doesn't find a match (N/A#). My table ranges
are: T3:U27, X3:Y48, AC3:AD134, AH3:AI69
This is the statement, after the modifications:

=IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T3:U27,,{0,4,5,5}
,{25,46,132,67}),E2)>0,0),T3:U27,X3:Y48,AC3:AD134,AH3:AI69),2,0),"")

P.S. You stated you hit CTL,Shift, Enter - what is this for?
Thanks so much!

"Domenic" wrote:

> Assuming that T3:U27, X3:Y48, AB3:AC27, and AF3:AG48 contain your
> tables, try...
>
> =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T3:U27,,{0,4,8,12}
> ,{25,46,25,46}),E2)>0,0),T3:U27,X3:Y48,AB3:AC27,AF3:AG48),2,0),"")
>
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Note that the
> first array constant...
>
> {0,4,8,12}
>
> ....determines the number of columns to move right from Column T for each
> table, and the second array constant...
>
> {25,46,25,46}
>
> ....determines the number of rows contained in each table. Change these
> accordingly. Post back if you need further help...
>
> In article <F3EC6E53-157C-4985-B6A0-2965BAF94100@microsoft.com>,
> lpj <lpj@discussions.microsoft.com> wrote:
>
> > How would i nest another 1 or 2 ranges in there? I'm looking at this syntax
> > but not getting very far - can i use 'or' operators? thanks again for your
> > help!
> >
> >
> > "Kassie" wrote:
> >
> > > Hi lpj
> > >
> > > Try the following formula:
> > >
> > > =IF(E2="","",IF(ISERROR(VLOOKUP('Code
> > > Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code
> > > Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
> > >
> > > --
> > > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South
> > > Africa
> > >
> > >
> > > "lpj" wrote:
> > >
> > > > Thanks for the reply! The reason I can't put it all in the same range is
> > > > bc
> > > > this is an existing worksheet (which can't be modified) and there are
> > > > some
> > > > columns of data in btwn that shouldn't be referrenced - they could create
> > > > duplicates or give invalid results back.
> > > >
> > > > "Kassie" wrote:
> > > >
> > > > > Hi lpj
> > > > >
> > > > > Not quite clear what you want to achieve here?
> > > > >
> > > > > Why not have everything in the same range?
> > > > >
> > > > > Am I correct in guessing that, if you do not find a matching record in
> > > > > the
> > > > > primary range, you then want to do a lookup in the secondary range? If
> > > > > so,
> > > > > you will have to use an If(OR( statement, to first look at the primary
> > > > > range,
> > > > > and then, if you do not find anything there, do a VLOOKUP in the
> > > > > secondary
> > > > > range. You will therefore have to test the primary range for an error
> > > > > condition, and if the error condition exists, then look at the
> > > > > secondary
> > > > > range, else look at the primary range. Again, why not put the whole
> > > > > lot in
> > > > > one range?
> > > > >
> > > > > --
> > > > > j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South
> > > > > Africa
> > > > >
> > > > >
> > > > > "lpj" wrote:
> > > > >
> > > > > > I currently have my Vlookup stmnt as this:
> > > > > >
> > > > > > =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE))
> > > > > >
> > > > > > 'Code Decrip' is the name of the worksheet
> > > > > > I need to add another range X$3:Y48
> > > > > > What is the proper syntax - I wasnt able to get it right after
> > > > > > searching
> > > > > > online for it.
> > > > > > Thanks so much.
>