multiple ranges on Vlookup

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