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