Multiple IF Statements

william.johnson

@Kelly Moore , yes this worked! Thanks!

IPC

Hi,

I'm trying to do a multiple IF formula but just can't get it to work. Any help is highly appreciated.

=IF((Status@row = " Not Started", "0%"),

IF(Status@row = " Initial Progress", "25%"),

IF(Status@row = " Mid-way Progress", "50%"),

IF(Status@row = " Major Progress", "75%"),

IF(Status@row = " Under approval", "90%"),100%)

@Andrée Starå

Thanks,

Irene

Kelly Moore

Hey @IPC

Your logic is correct however typically the Status column is looking for percentages written as decimals. Note that numbers generally are not enclosed in quotes as smartsheet might confuse them as Text.

When nesting IF statements, note how the IF statement isn't closed off until the very end. As a rule of thumb you will have as many closing parentheses as you do the number of 'IFs'.

=IF((Status@row = " Not Started", 0, IF(Status@row = " Initial Progress", 0.25, IF(Status@row = " Mid-way Progress", 0.5, IF(Status@row = " Major Progress", 0.75, IF(Status@row = " Under approval", 0.9, 1))))

Does this work for you?

Kelly

IPC

Hey Kelly,

Thank you very much! yes it worked.

Most important part of the learning " As a rule of thumb you will have as many closing parentheses as you do the number of 'IFs'." This is great.

Kind regards,

Irene

carlosreyeskpqw841

https://community.smartsheet.com/discussion/29591/multiple-if-statements

Hi,

I have the same need, but this formula is not working for me, any thoughts? I keep getting "invalid operation"

Genevieve P.

Hi @carlosreyeskpqw841

Can you post the formula that you're currently attempting to use, and a screen capture of your sheet (but block out sensitive data)?

Thanks!

Genevieve

carlosreyeskpqw841

Thanks Genevieve,

This is what I'm using:

=IF([Total Accrual %]@row = 1, "Green", =IF([Total Accrual %]@row = 0, "Red", "Yellow"))

Genevieve P.

Hi @carlosreyeskpqw841

It looks like you just have an extra = sign in there which is causing the error! 🙂

Try this:

=IF([Total Accrual %]@row = 1, "Green", IF([Total Accrual %]@row = 0, "Red", "Yellow"))

Cheers,

Genevieve

carlosreyeskpqw841

https://community.smartsheet.com/discussion/comment/372009#Comment_372009

thank you! it worked :)

Bosakie

I have the same issues, but for the expression I am using a number and if it is greater equal to 89 the result will be Dark Blue, the next if statement is greater equal to 78 it will be blue, or else Yellow.

=IF([Effort]@row >= 89, "Dark Blue", IF([Effort]@row >= 78, Blue, "Yellow"))

I get an unparseable error

I want to continue the formula with additional nested if statements, but need to get the format of the nested if statement down first,

Thanks in advance

Genevieve P.

Hey @Bosakie

It looks like you're just missing the quotes around "Blue" in the second statement:

=IF([Effort]@row >= 89, "Dark Blue", IF([Effort]@row >= 78, "Blue", "Yellow"))

Cheers,

Genevieve

Bosakie

Thank you,

Jen Stover

Please help, I feel like I am running into the same is above but am not finding the solution. I am trying to nest a Vlookup in an If statement. I was able to get it to pull from the first If but it ignoring the second rule.

=IF([Manufacture Assigned]@row = "[contact 2]#", VLOOKUP([Location Number]@row, {Repair dispatch sheet Range 4}, 13, false), VLOOKUP([Location Number]@row, {Repair dispatch sheet Range 4}, 11, false))

=IF([Manufacture Assigned]@row = "[contact 2]#", VLOOKUP([Location Number]@row, {Repair dispatch sheet Range 4}, 13, false), IF([Manufacture Assigned]@row = "[Contact 1]#", VLOOKUP([Location Number]@row, {Repair dispatch sheet Range 4}, 11, false))) This one is coming back Unparseable

Genevieve P.

Hi @Jen Stover

Can you share a screen capture of the sheet you're placing the formula in?

Also, it looks like your ranges are quite large, if you're looking at the 13th column or the 11th column. I'd highly recommend using an INDEX(MATCH combination instead of VLOOKUP as this way you're only referencing TWO individual columns instead of a range across 13. Even though all the middle columns aren't necessary for the output, they're still being referenced which bumps up your Referenced Cell number as well as making the formula slower to load.

Here's an example of an INDEX(MATCH

=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

So in your case for your first VLOOKUP, you'd replace it with:

=INDEX({Column to return}, MATCH([Location Number]@row, {Location Column Reference}, 0))

Then the next thing I'd check in your formula is if you're looking for the text "[contact 2]#" or if you're trying to reference the Sheet Summary Field called contact 2.

If you're referencing a Sheet Summary Field, you'll want to ensure there are no quotes around the reference.

Try a full formula like this:

=IF([Manufacture Assigned]@row = [contact 2]#, INDEX({Column to return}, MATCH([Location Number]@row, {Location Number Reference}, 0)), IF([Manufacture Assigned]@row = [Contact 1]#, INDEX({Different Column to return}, MATCH([Location Number]@row, {Location Number Reference}, 0))))

See:

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations#toc-lookup-one-cell-using-a-matching-value

Cheers,

Genevieve

Jen Stover

@Genevieve P. Thank you so much it was as simple as the quotes around the summary contact. Removed it and now everything works perfect!