Vlookup a value if blank cell found after meeting two other criteria

Hi

I had help the other day with a formula to return a value if a cell was at zero or greater than cell E. Here is the formula:

=VLOOKUP(A22,Qualifications!$A$4:$Y$152,19-2*(OR(VLOOKUP(A22,Qualifications!$A$4:$Y$152,19,0) >E22, VLOOKUP(A22,Qualifications!$A$4:$Y$152,19,0)=0)),0 ))

This works great, but on occasion the returned cell value to be displayed (when the vlookup is at zero or greater than E) is a blank cell. In this situation, I want to display the cell value which is in column 13 (and not column 17 as the formula is set up for). Basically, I need to add a piece to the formula for when there is a blank cell to return a value a few columns back.

Regards,

Mac

Try changing to

=VLOOKUP(A22,qualifications!$A$4:$Y$152,19-IF(OR(VLOOKUP(A22,qualifications!$A$4:$Y$152,19,0)>E22,VLOOKUP(A22,qualifications!$A$4:$Y$152,19,0)=0),IF(VLOOKUP(A22,qualifications!$A$4:$Y$152,17,0)="",6,2),0),0)

Thanks for getting back to me. I tried the formula and it doesn't work - It returns a value that is not in the row being searched. I tried to decipher your formula but can't quite get my head around what it means. Just in case you can't quite get the picture, I am looking at col 19 for a date. If that date is equal to zero or greater than the date in cell E22, then I want to return the value in col 17. If, however, col 17 is also a blank cell, I want to return the value in col 13.

Many thanks,


Mac

Daddylonglegs

My apologies to you - Like an idiot, I copied your formula to the wrong cell, which is why it was throwing all sorts of figures at me. Once again, you have come to my rescue. Many thanks to you, especially for getting back so quickly and with the correct solution.

Kind regards,

Mac