Forum Discussion

Copper Contributor

Mar 01, 2021

Solved

Add IF layer to a established Lookup formula

This is my working formula for the spreadsheet:

Cell F2 =LOOKUP(DAYS360(B2,TODAY()),{-1000,3,7,30},{"Less than 3 days","Less than 7 days","Less than 30 days","Over 30 days"})

I need to add a layer to this formula:

In column D2 the cells have either "Yes" or "No" in them. If the cell has yes then the response in Cell F2 needs to say "complete". Only if cell D2 says "No" would I need the answers from my above lookup.

excel

Formulas and Functions

Twifoo

Mar 01, 2021

NoraJJ

You simply add IF in F2, like this:

=IF(D2="Yes","Complete",
LOOKUP(DAYS360(B2,TODAY()),{-1000,3,7,30},{"Less than 3 days","Less than 7 days","Less than 30 days","Over 30 days"}))

5 Replies

Twifoo
Silver Contributor
Mar 01, 2021
NoraJJ
You simply add IF in F2, like this:
=IF(D2="Yes","Complete", LOOKUP(DAYS360(B2,TODAY()),{-1000,3,7,30},{"Less than 3 days","Less than 7 days","Less than 30 days","Over 30 days"}))
- NoraJJ
  Copper Contributor
  Mar 01, 2021
  Thank you for helping me understand this.
  - Twifoo
    Silver Contributor
    Mar 01, 2021
    Helping you is my pleasure!
mathetes
Gold Contributor
Mar 01, 2021
Try =IFS(D2="Yes","Complete",D2="No",LOOKUP(DAYS360(B2,TODAY()),{-1000,3,7,30},{"Less than 3 days","Less than 7 days","Less than 30 days","Over 30 days"}))

Note that this is the IFS function, not IF'; so you could add other conditions for cell D2
- NoraJJ
  Copper Contributor
  Mar 01, 2021
  Thank you for your time and input.