how about using left and right something like this idea

=VLOOKUP(A3,IF(LEFT(A2)="y",D4:E6,E4:F6),IF(RIGHT( A2)="n",1,2))
--
Don Guillett
SalesAid Software

"tlosgyl3" wrote in
message ...

Using Lookup I want to return a value to a cell based upon 2 conditions,
which can exist in any one of four combinations:
YY
YN
NY
YN

The first letter determines which of two Lookup tables are used (Lookup
High or Lookup Std) the second determines which column in the relevant
Lookup table to use for the value to be returned.

I have the two following expressions each of which returns a correct
value but only if the first letter condition is correct (Y or N).

=IF($F2="YN",((INDEX('Lookup High'!$C$2:$D$45,MATCH(AM2,'Lookup
High'!$C$2:$C$45,),MATCH(BM$1,'Lookup
High'!$C$2:$D$2,)))),IF($F2="YY",(INDEX('Lookup
High'!$E$2:$F$45,MATCH(AM2,'Lookup
High'!$E$2:$E$45,),MATCH(BM$1,'Lookup High'!$E$2:$F$2,)))))

=IF($F2="NN",((INDEX('Lookup STD'!$C$2:$D$45,MATCH(AM2,'Lookup
STD'!$C$2:$C$45,),MATCH(BM$1,'Lookup
STD'!$C$2:$D$2,)))),IF($F2="NY",(INDEX('Lookup
STD'!$E$2:$F$45,MATCH(AM2,'Lookup STD'!$E$2:$E$45,),MATCH(BM$1,'Lookup
STD'!$E$2:$F$2,)))))

When I combine these:

=IF($F2="YN",((INDEX('Lookup High'!$C$2:$D$45,MATCH(AM2,'Lookup
High'!$C$2:$C$45,),MATCH(BM$1,'Lookup
High'!$C$2:$D$2,)))),IF($F2="YY",(INDEX('Lookup
High'!$E$2:$F$45,MATCH(AM2,'Lookup
High'!$E$2:$E$45,),MATCH(BM$1,'Lookup
High'!$E$2:$F$2,))))),IF($F2="NN",((INDEX('Lookup
STD'!$C$2:$D$45,MATCH(AM2,'Lookup STD'!$C$2:$C$45,),MATCH(BM$1,'Lookup
STD'!$C$2:$D$2,)))),IF($F2="NY",(INDEX('Lookup
STD'!$E$2:$F$45,MATCH(AM2,'Lookup STD'!$E$2:$E$45,),MATCH(BM$1,'Lookup
STD'!$E$2:$F$2,)))))

Excel gives the result #VALUE!

Any help in spotting the error would be appreciated.

I would also like to return the value 0 if none of the conditions are
met.

Thanks in advance
Tlosgyl3



--
tlosgyl3
------------------------------------------------------------------------
tlosgyl3's Profile:
http://www.excelforum.com/member.php...o&userid=28074
View this thread: http://www.excelforum.com/showthread...hreadid=559277