Forum Discussion

Copper Contributor

Apr 30, 2019

How to exclude cells that contains formula from the range of cells in a LOOKUP function

Hello, I am currently preparing a payment milestone template which also records the last/latest date of payment. I used the this formula to do just that, =LOOKUP (2,1/ (J4:LN4<>"",$J$2:$LN$2)). B...

Payment Milestone template.xlsx37 KB

LOOKUP FUNCTION

range

MrNobody

Copper Contributor

Apr 30, 2019

Celia_Alves

This is great stuff, finally it works !!

The remaining problem is if there is no data in the row, it still returns a date, I hope you can help me solve this one.

Thank you again

SergeiBaklan

Diamond Contributor

Apr 30, 2019

MrNobody , as variant you may expand your LOOKUP with one more filter

=IFERROR(LOOKUP(2,1/(J4:LN4<>"")/(($J$1:$LN$1="")),$J$2:$LN$2),"")

Payment Milestone template.xlsx37 KB

Celia_Alves
MVP
May 01, 2019
Nice one, SergeiBaklan! As usual. ;-)
- SergeiBaklan
  Diamond Contributor
  May 01, 2019
  Celia_Alves , thank you
  - MrNobody
    Copper Contributor
    May 03, 2019
    SergeiBaklan Celia_Alves
    
    Thank you guys for the big help means a lot