The formula should be as follows...

=IF(E2<"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OF FSET(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(OF FSET(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 ,
lpj 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
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!