Nested If Returns Error

[wsnyder]

Hi all,

I have a Nested If Formula that returns an error sometimes even though the pattern being sought does exist.
I chose a few samples at random that return errors. I created a single line IF statement based on the content of A and the correct result was returned.

Ideas what I am missing here?

Thanks,
-w

=IF(AND(FIND("Fruit",A162),FIND("Group A",A162)),"Fruit-A",
    IF(AND(FIND("Fruit",A162),FIND("Group B",A162)),"Fruit-B",
      IF(AND(FIND("Fruit",A162),FIND("Group C",A162)),"Fruit-C",
         IF(AND(FIND("Fruit",A162),FIND("Group D",A162)),"Fruit-D","Missing"
             )
           )
         )
       )

 

[awoohaw]

well, you don't have any contingencies for when the Fruit + (Any of Group A:D)) is not found,,, you only have the Missing for when
Fruit + GroupA is true, AND Fruit + GroupB is True, but Fruit + Group C is FALSE >>> you'll get MISSING FOR THAT.
You will Not Get missing if on "Anything But Fruit" or Fruit + Not GroupA, or Fruit + GroupA AND Fruit + Not GroupB.... you will get FALSE.
I'm not sure why you would get actual ERROR messages though, if that is what you get.

Of course you can post a mini worksheet with your data and formulas using hte xl2bb add in (Link Below) and the forum can work try to solve the issue.

 

[awoohaw]

Try this:

mr excel questions 29.xlsm
	A	B	C
161
162	Fruit Group A		Fruit-A
163	Fruit Group B		Fruit-B
164	Fruit Group C		Fruit-C
165	Fruit Group D		Fruit-D
166	Fruit Group E		Missing
167	Vegetable Group A		No Fruit
wsnyder

Cell Formulas
Range	Formula
C162:C167	C162	= IF(ISNUMBER(FIND("Fruit",A162)), IF(ISNUMBER(FIND("Group A",A162)),"Fruit-A", IF(ISNUMBER(FIND("Group B",A162)),"Fruit-B", IF(ISNUMBER(FIND("Group C",A162)),"Fruit-C", IF(ISNUMBER(FIND("Group D",A162)),"Fruit-D","Missing" ) ) ) ),"No Fruit")

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[wsnyder]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks Peter, I updated Account Details
-w

 

[wsnyder]

Try this:

mr excel questions 29.xlsm
	A	B	C
161
162	Fruit Group A		Fruit-A
163	Fruit Group B		Fruit-B
164	Fruit Group C		Fruit-C
165	Fruit Group D		Fruit-D
166	Fruit Group E		Missing
167	Vegetable Group A		No Fruit
wsnyder

Cell Formulas
Range	Formula
C162:C167	C162	= IF(ISNUMBER(FIND("Fruit",A162)), IF(ISNUMBER(FIND("Group A",A162)),"Fruit-A", IF(ISNUMBER(FIND("Group B",A162)),"Fruit-B", IF(ISNUMBER(FIND("Group C",A162)),"Fruit-C", IF(ISNUMBER(FIND("Group D",A162)),"Fruit-D","Missing" ) ) ) ),"No Fruit")

Thanks awoohaw,
I decided to go with a different approach instead of Nested IF's.
I created a criteria table and added a formula to reference the criteria in the table.
My initial formula:

=IFERROR(IF(AND(SEARCH(G$4,$A8),SEARCH(G$5,$A8)),G$3,NA()),NA())

The end formula that brings it altogether after 9 columns (F:N)

=INDEX($F8:$N8,1,MATCH(FALSE,ISNA($F8:$N8),0))

It works pretty well. It's easy to undertand and its easy to maintain.
However, I am running into 1 instance where 2 different set of criteria are retuning a result, so I would like to check all previous cells on a row, if a previous cell has text, then the current cell should return NA(). However, my current formula is returning a #SPILL! error when I give the cell Array: ISTEXT($F8:F8) so by the eight cell the Array is ($F8:N8)
How can I write the formula so it will check all previous cells on the Row where the Text Value can be as much as 8 Cells to the left?

Current formula:

=IFERROR(IF(ISTEXT($F8:F8),NA(),
    IF(AND(SEARCH(G$4,$A8),SEARCH(G$5,$A8)),G$3,NA()
    )
  ),
NA()
)

Thanks,
-w

 

[awoohaw]

update the istext portion to this:

SUM(--ISTEXT($F8:F8))>0

so:

=IFERROR(IF(   SUM(--ISTEXT($F8:F8))>0     ,NA(),
    IF(AND(SEARCH(G$4,$A8),SEARCH(G$5,$A8)),G$3,NA()
    )
  ),
NA()
)

 

[wsnyder]

update the istext portion to this:

SUM(--ISTEXT($F8:F8))>0

so:

=IFERROR(IF(   SUM(--ISTEXT($F8:F8))>0     ,NA(),
    IF(AND(SEARCH(G$4,$A8),SEARCH(G$5,$A8)),G$3,NA()
    )
  ),
NA()
)

Thanks awoohaw, Works perfectly!

-w

 

[awoohaw]

I'm pleased you found a solution at the Mr. Excel Forum.

Best Wishes!