Vlookup with two criteria and if met do a second vlookup

[markmac234]

Hi

I have a vlookup which has two criteria - if the returned value is greater than cell E3 (which is a date on the same sheet as the vlookup) or equal to zero, then vlookup the column preceding it. I have the following formula so far which just compares cell E3

=IF(VLOOKUP(A3,Qualifications!$A$3:$S$104,5,0)>E3,VLOOKUP(A3,Qualifications!$A$3:$S$104,4,0),VLOOKUP(A3,Qualifications!$A$3:$S$104,5,0)))

Kind regards,

Mac

[daddylonglegs]

Hello Mac,

This is one way....

=VLOOKUP(A3,qualifications!$A$3:$S$104,5-(OR(VLOOKUP(A3,qualifications!$A$3:$S$104,5,0)>E3,VLOOKUP(A3,qualifications!$A$3:$S$104,5,0)=0)),0)

[markmac234]

daddylonglegs

Thanks a million - You are a star. One final question on the matter.....if you wanted to return a value in a column (let's say 2 back and not just the 1), what would you write in the formula. You have it working perfectly for the preceding column, but what about the one before that one. eg. columns 3 and 5 and not 4 and 5 as your formula states?

Regards,

Mac

[daddylonglegs]

Change to

=VLOOKUP(A3,qualifications!$A$3:$S$104,5-2*(OR(VLOOKUP(A3,qualifications!$A$3:$S$104,5,0) >E3, VLOOKUP(A3,qualifications!$A$3:$S$104,5,0)=0)),0)

[markmac234]

Now that's just the ticket - I can sleep now.
Many thanks to you.

Mac