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
a T3:U27, X3:Y48, AC3:AD134, AH3:AI69
This is the statement, after the modifications:

=IF(E2<"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OF FSET(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(OF FSET(T3:U27,,{0,4,8,12}
,{25,46,25,46}),E2)0,0),T3:U27,X3:Y48,AB3:AC27,AF 3: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 ,
lpj 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))

--
ve_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?

--
ve_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.