Column L Column M
306TRFKM (THREE FORKS MIDDLE) B
306TRFKM (THREE FORKS MIDDLE) B
306TRFKL (THREE FORKS LOWER) #VALUE!
Formula for column M
=IF(FIND("MIDDLE",L1,1)>0,"B",IF(FIND("LOWER",L1,1)>0,"A"))
The formula only looks at the first "IF" statement and ignores the second. If I switch the IF statement so that I'm looking for "LOWER" first, it works for all the lowers but ignores the second IF and gives me a #Value for all of the middles.
Any idea why this is happening?
Can you post a real workbook with sample data and a proposed outcome you are looking for?
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
You are telling the formula to find middle, not asking it whether middle exists. You could try an IFERROR in there if Middle and Lower are the only conditions
=IFERROR(IF(FIND("MIDDLE",A4,1)>0,"B",""),"A")
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
FIND is case sensitive. Could that be the problem? If so use the SEARCH function
Or =IF(ISNUMBER(FIND("MIDDLE",L1,1)),"B",IF(ISNUMBER(FIND("LOWER",L1,1)),"A"))
Last edited by Pepe Le Mokko; 07-25-2014 at 11:53 AM.
If this works
=IF(FIND("COST",J12,1)>0,"Found it",IF(FIND("YALL",J12,1)>0,"found it again","havent found it"))
I don't know why its working, just reconstructed it from 0.
If it doesn't work - its because it doesn't find what it's looking for - and I don't know how to fix it.
Last edited by Polymorpher; 07-25-2014 at 11:58 AM.
Thanks for the response. I tried SEARCH and I get the same result.
It crashes on failure to find it. Its either a bug or some god forsaken rule excel has. more likely a rule tough. These kinds of problems I usually solve by running over the help file for the functions that are crashing on me.
There are more than 2 conditions.
Did you try =IF(ISNUMBER(FIND("MIDDLE",L1)),"B",IF(ISNUMBER(FIND("LOWER",L1)),"A"))
Good, got me of the hook was testing it (:
And yeah find is case sensitive.
Last edited by Polymorpher; 07-25-2014 at 12:10 PM.
That worked Pepe!!! Thanks for your help.
So why does this work and the original formula not work?
Try this..
A definite solution..
Formula:
Please Login or Register to view this content.
Drag it down...
Don't forget to click *
First be aware of how XL compares numbers, text, etc..
In fact XL first evaluates the value of the TYPE() of both parts of the comparison. ( see XL help for syntax of the TYPE() function) and then the values if both TYPE() functions return the same value.
So, if FIND finds the string, and returns a number, everything is as should. ( both vales of the TYPE() function are equal, XL then compares the values of the returned number returning TRUE )
If FIND (or SEARCH) does NOT find the result, it returns an error value #VALUE. As you can see the TYPE() function of an error returns 64 which is larger than the TYPE() value of 0 ( which is 1.
So, using the FIND function as you did always returns TRUE and the FALSE part is never used ( as you experienced)
Using the ISNUMBER function to embed FIND we can solve this problem
Last edited by Pepe Le Mokko; 07-25-2014 at 02:28 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks