multiple ranges on Vlookup

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.

Thanks so much - to all of you! I really appreciate it!

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

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.

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.

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

The formula should be as follows...

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

If you have no other data below your tables and you want to use whole
column references, use the following formula instead...

=IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T:U,,{0,4,9,14}),E
2)>0,0),T:U,X:Y,AC:AD,AH:AI),2,0),"")

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
just ENTER. In other words, after typing the formula, instead of
pressing just ENTER, hold the CONTROL+SHIFT keys down and while those
two keys are pressed down press ENTER. Excel will automatically place
braces {} around the formula which will indicate that you've entered it
correctly.

In article <B9401B53-9F93-47AE-A332-69F92000DEE6@microsoft.com>,
lpj <lpj@discussions.microsoft.com> wrote:

> 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!

i dont know - still having problems - could this be bc the name of the sheet
these tables are on is no longer in the formula? 'Code Descrip'? because
where this formula is going is not on that sht (code descrip). thanks so muxh
for all your help.

"Domenic" wrote:

> The formula should be as follows...
>
> =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T3:U27,,{0,4,9,14}
> ,{25,46,132,67}),E2)>0,0),T3:U27,X3:Y48,AC3:AD134,AH3:AI69),2,0),"")
>
> If you have no other data below your tables and you want to use whole
> column references, use the following formula instead...
>
> =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T:U,,{0,4,9,14}),E
> 2)>0,0),T:U,X:Y,AC:AD,AH:AI),2,0),"")
>
> Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
> just ENTER. In other words, after typing the formula, instead of
> pressing just ENTER, hold the CONTROL+SHIFT keys down and while those
> two keys are pressed down press ENTER. Excel will automatically place
> braces {} around the formula which will indicate that you've entered it
> correctly.
>
> In article <B9401B53-9F93-47AE-A332-69F92000DEE6@microsoft.com>,
> lpj <lpj@discussions.microsoft.com> wrote:
>
> > 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!
>

Yea - that is def. the issue - not having the sheet name in the formula (bc i
tested it - putting the lookup info on the same sheet) - how would i go about
getting it in the formula? i looked at our previous posts but not getting far
as the formula (and its syntax) has changed quite some. thanks again!


"Domenic" wrote:

> The formula should be as follows...
>
> =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T3:U27,,{0,4,9,14}
> ,{25,46,132,67}),E2)>0,0),T3:U27,X3:Y48,AC3:AD134,AH3:AI69),2,0),"")
>
> If you have no other data below your tables and you want to use whole
> column references, use the following formula instead...
>
> =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T:U,,{0,4,9,14}),E
> 2)>0,0),T:U,X:Y,AC:AD,AH:AI),2,0),"")
>
> Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
> just ENTER. In other words, after typing the formula, instead of
> pressing just ENTER, hold the CONTROL+SHIFT keys down and while those
> two keys are pressed down press ENTER. Excel will automatically place
> braces {} around the formula which will indicate that you've entered it
> correctly.
>
> In article <B9401B53-9F93-47AE-A332-69F92000DEE6@microsoft.com>,
> lpj <lpj@discussions.microsoft.com> wrote:
>
> > 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!
>

got it on my own - thanks for all of your help!


"Domenic" wrote:

> The formula should be as follows...
>
> =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T3:U27,,{0,4,9,14}
> ,{25,46,132,67}),E2)>0,0),T3:U27,X3:Y48,AC3:AD134,AH3:AI69),2,0),"")
>
> If you have no other data below your tables and you want to use whole
> column references, use the following formula instead...
>
> =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T:U,,{0,4,9,14}),E
> 2)>0,0),T:U,X:Y,AC:AD,AH:AI),2,0),"")
>
> Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
> just ENTER. In other words, after typing the formula, instead of
> pressing just ENTER, hold the CONTROL+SHIFT keys down and while those
> two keys are pressed down press ENTER. Excel will automatically place
> braces {} around the formula which will indicate that you've entered it
> correctly.
>
> In article <B9401B53-9F93-47AE-A332-69F92000DEE6@microsoft.com>,
> lpj <lpj@discussions.microsoft.com> wrote:
>
> > 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!
>

Sorry! I forgot about the sheet reference. But I'm glad you've got it
sorted out.

Cheers!

In article <6BCC9DDE-CDFD-40BC-9C3D-F5FB28E0E645@microsoft.com>,
lpj <lpj@discussions.microsoft.com> wrote:

> got it on my own - thanks for all of your help!
>
>
> "Domenic" wrote:
>
> > The formula should be as follows...
> >
> > =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T3:U27,,{0,4,9,14}
> > ,{25,46,132,67}),E2)>0,0),T3:U27,X3:Y48,AC3:AD134,AH3:AI69),2,0),"")
> >
> > If you have no other data below your tables and you want to use whole
> > column references, use the following formula instead...
> >
> > =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T:U,,{0,4,9,14}),E
> > 2)>0,0),T:U,X:Y,AC:AD,AH:AI),2,0),"")
> >
> > Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
> > just ENTER. In other words, after typing the formula, instead of
> > pressing just ENTER, hold the CONTROL+SHIFT keys down and while those
> > two keys are pressed down press ENTER. Excel will automatically place
> > braces {} around the formula which will indicate that you've entered it
> > correctly.
> >
> > In article <B9401B53-9F93-47AE-A332-69F92000DEE6@microsoft.com>,
> > lpj <lpj@discussions.microsoft.com> wrote:
> >
> > > 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!
> >

Sorry! I forgot about the sheet reference. But I'm glad you've got it
sorted out.

Cheers!

In article <6BCC9DDE-CDFD-40BC-9C3D-F5FB28E0E645@microsoft.com>,
lpj <lpj@discussions.microsoft.com> wrote:

> got it on my own - thanks for all of your help!
>
>
> "Domenic" wrote:
>
> > The formula should be as follows...
> >
> > =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T3:U27,,{0,4,9,14}
> > ,{25,46,132,67}),E2)>0,0),T3:U27,X3:Y48,AC3:AD134,AH3:AI69),2,0),"")
> >
> > If you have no other data below your tables and you want to use whole
> > column references, use the following formula instead...
> >
> > =IF(E2<>"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OFFSET(T:U,,{0,4,9,14}),E
> > 2)>0,0),T:U,X:Y,AC:AD,AH:AI),2,0),"")
> >
> > Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
> > just ENTER. In other words, after typing the formula, instead of
> > pressing just ENTER, hold the CONTROL+SHIFT keys down and while those
> > two keys are pressed down press ENTER. Excel will automatically place
> > braces {} around the formula which will indicate that you've entered it
> > correctly.
> >
> > In article <B9401B53-9F93-47AE-A332-69F92000DEE6@microsoft.com>,
> > lpj <lpj@discussions.microsoft.com> wrote:
> >
> > > 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!
> >

just wanted to add - i looked up ctl+sht_enter (array enter the formula), and
i did do this - i noticed after doing so my formula was within brackets - but
still getting not getting the results expected (unless the value is null,
then i get null which i want). Thanks.

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