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