Nested IF formulas

**SEHRET** · 01-20-2012, 05:56 PM

I have a spreadsheet with tons of conditional formatting underlying in several cells. I don't know if it is possible what I want to do, which is

either of the following IF formulas apply.

IF(N557="pending","X","R/P")

(IF(N557="expected","X","X") ... but the word "expected" is followed by a date/word, i.e. the following items vary, the only constant is the word "expected"

I'm wondering, how to put both IF's in the cell, i.e. either applies, but how to write that cell N557 "contains" the word EXPECTED?
FYI: I cannot use date only, because the cell will ultimately have a date, so the conditional formatting won't work using a date only instead of putting the word "expected" in front of it.

Maybe I'm throwing conditional formatting and formulas together, which may not work. But I appreciate any ideas!
Thanks so much in advance.

**Andrew-R** · 01-20-2012, 06:04 PM

The FIND function finds text within a cell, and returns an error if it's not found, so:

=IF(ISERROR(FIND("expected",N557)),"Value if not found","Value if found")

**Philb1** · 01-20-2012, 06:10 PM

Hi Sehret.. Welcome to the forum
You can try this as well

=IF(LEFT(N557,8)="Expected","X","X")

Have fun

**SEHRET** · 01-24-2012, 10:43 AM

Thanks for your fast responses, but both won't work on my scenario - or I'm just not seeing it. What I need to do is to get both IF formulas in the same cell in column "S". Currently I only have one formula in the column, but need both in the same cell. Basically a double IF, i.e. if one IF doesn't apply, then apply the other IF. I have no clue how to write it. I've attached a simple screen print.

Basically column "R" right now has =IF(N609="pending","X","R/P"). "X" is just a parameter and conditionally formatted in white, so that the cell R609 appears to be empty, if N609 is empty. "R/P" stands for "Received/Processing", i.e. R/P appears as formatted in orange, when N609 contains something other than the word "pending", i.e. a date in this case.

But I also want to add a formula, that if N609 contains the word "expected", it will show "X" for in column "R" for TRUE and FALSE value, so it again can be formatted as white, i.e. not visually showing.

I just don't know how to get both IF's together into one formula. Maybe I'm not seeing the bigger picture. My intend was to avoid to add another column for the "expected" date to a spreadsheet that's getting big. Column "R" has about 6 different conditional formattings underlying for several other, more simple criteria.

IF formulas.jpg

**Philb1** · 01-25-2012, 02:41 AM

Have a look at this & see if it's what you're after

=IF(N557="","",IF(LEFT(N557,7)="Pending","X",IF(LEFT(N557,8)="Expected","X","R/P")))

If N557 is empty, R557 is blank, If N557 has Expected & anything else if you want, such as a date etc, it returns X, If N557 has Pending & anything else, it returns X, If N557 has anything else in it, it returns R/P. I've attached a sample file for you. Scroll down to row 557 to try it
Cheers