Share via

Use multiple isblank with then statements

Anonymous
2015-09-02T19:19:59+00:00

Hi, I have 4 columns with a value in only one column, on a single row.  Here is what I want to do

If the cell is blank, check the next cell, but if the cell is not blank then return 20%

=IF(ISBLANK(M2),"",20%)

If the cell is blank, check the next cell, but if the cell is not blank then return 40% 

=IF(ISBLANK(N2),"",40%)

If the cell is blank, check the next cell, but if the cell is not blank then return 60%

=IF(ISBLANK(O2),"",60%)

If the cell is blank, check the next cell, but if the cell is not blank then return 80%

=IF(ISBLANK(P2),"",80%)

These work just fine when used separately, but I want to combine all of those functions into a single formula.  Can you help?  Thank you!

Microsoft 365 and Office
Microsoft 365 and Office

A comprehensive suite of productivity tools and cloud services that enhance collaboration, communication, and efficiency. Combining classic Office apps with advanced Microsoft 365 features, it supports both personal and business needs

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-09-02T20:04:23+00:00

    Please use this formula :

    =IF(NOT(ISBLANK(M2)),"20%",IF(NOT(ISBLANK(N2)),"40%",IF(NOT(ISBLANK(O2)),"60%",IF(NOT(ISBLANK(P2)),"80%",""))))

    I have created a sample for you and worked fine

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2015-09-02T19:54:35+00:00

    =IF(M2<>"",20%,IF(N2<>"",40%,IF(O2<>"",60%,IF(P2<>"",80%,""))))

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  3. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2015-09-02T23:12:40+00:00

    Hi,

    In cell Q2, enter this formula and copy down.

    =0.2*MATCH(TRUE,INDEX(M2:P2<>"",,),0)

    Hope this helps.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2015-09-02T20:17:24+00:00

    Hi,

    Try it this way. Format the formula cell as %

    =IFERROR(CHOOSE(MATCH(TRUE,INDEX((M2:P2<>0),0),0),0.2,0.4,0.6,0.8),"")

    Was this answer helpful?

    0 comments