Finding a specific word within cell

[Vespasian]

Please Login or Register  to view this content.

Spent FIVE HOURS on the problem, worst Excel riddle in my life that seems to defy laws of logic. I'm gutted to let the problem defeat me:

I want to find PUMP and only PUMP in the search fields B3:B5
For cells C3:C5, they return FALSE if PUMP and is not found. They return A3:A5 if PUMP found.

As you can see cell C5 should return A5 but doesnt! And its formulae for C5 is:

=IF(AND(IFERROR(FIND(C$2,$B5,1),FALSE)=TRUE,IFERROR(FIND($C$1,$B5,1),FALSE)=FALSE)=TRUE,$A5,FALSE)

Can anyone explain why its not finding it?

[shg]

How about just

=if(B5="pump", A5)

[Vespasian]

How about just

=if(B5="pump", A5)

shg, the problem with that is that then it will pick out B3 which is PUMP MOTOR which I DONT want to pick out as its another item or description. Its about identifying PUMP and PUMP only. Whereas PUMP MOTOR is another unique search word.

Like I said the last formale just has to return the value in A5. Defies logic.

[shg]

Did you try the formula, or are you speculating?

[witchcraftz]

You could try to use MATCH()

[Vespasian]

Did you try the formula, or are you speculating?

It works but only for that example because it it just so happens that PUMP was the first word. What If I had this:

ABC PUMP MOTOR
AXLE
EFGH PUMP

instead as fields B3:B5?

[witchcraftz]

Put this in C1

=IF(MATCH("pump",B1,0)=1,A1)

MATCH() is not case sensitive though, this can be good or bad. If you use MATCH(value,range,0) then you can use wildcards in the search.

[FDibbins]

It works but only for that example because it it just so happens that PUMP was the first word. What If I had this:

ABC PUMP MOTOR
AXLE
EFGH PUMP

instead as fields B3:B5?

what would you expect the answer to be in this case?

@ witchcraftz in your suggested formula..."MATCH(value,search,1)" I think you have things the wrong way round perhaps? the syntax for match() is =match(value,range,match-type)

Also search() is not case-sensitive, but find() is

[witchcraftz]

Sorry I didn't meant "search" but "range" as you pointed out was the syntax.

The solution provided by shg is the easiest. I was just pointing out another solution possible.

If you want to find any kind of pump in the B column you would use the wildcards in match.
=IF(MATCH("*pump*",B1,0),A1)
This would find all the occurrences of "pump".

[FDibbins]

OK thanks for clearing that up

[Vespasian]

what would you expect the answer to be in this case?

@ witchcraftz in your suggested formula..."MATCH(value,search,1)" I think you have things the wrong way round perhaps? the syntax for match() is =match(value,range,match-type)

Also search() is not case-sensitive, but find() is

I expect C1 and C2 to not find anything but C3 to give an answer having being found the word PUMP.
C1, although contains the word PUMP, is actually part of a bigger word PUMP MOTOR, which MUST NOT be counted.

I tried all of the above, still doesnt work!!!

[FDibbins]

SA far as excel is concerned, "pump" is "pump", so unless you can specify to only search for pump with anything before it and not for anything after it, Im not sure how to build a formula around what you want

ABC PUMP MOTOR - dont pick (words after pump)
AXLE - dont pick (no pump)
EFGH PUMP pick (no words after pump)

[Vespasian]

FDibbins, I propose there is a way around it and it was my opening formulae that had the correct solution. It is correct because it conforms to logic (at least what I can see) but excel doesn't execute it?

That, is, I search for Pump in the cell AND I also search for Pump motor in the cell. IF it finds Pump but NOT Pump motor, then it has found pump!

And that is encapsulated in the equation below:

=IF(AND(IFERROR(FIND(C$2,$B5,1),FALSE)=TRUE,IFERROR(FIND($C$1,$B5,1),FALSE)=FALSE)=TRUE,$A5,FALSE)

BUT, it returns not found, which I cannot, simply cannot explain as it defies logical TRUE FALSE and AND expressions. I have to be wrong in that equation or else obviously it would work, but I cannot see whats wrong in that equation that doesnt give the correct answer.

I'm utterly baffled.

[FDibbins]

ok, if MOTOR is the only word you are going to exclude, try this...

=AND(ISNUMBER(SEARCH("PUMP",B3,1)),NOT(ISNUMBER(SEARCH("MOTOR",B3,1))))

replace PUMP and MOTOR with whatever cell references you have

[vlady]

simply cannot explain as it defies logical TRUE FALSE and AND expressions

the search/find function is returning 1 for true and 0 for false then you are comparing to text true/false you can try to use the numbers 0 and 1 for the logical If(and..

[Vespasian]

ok, if MOTOR is the only word you are going to exclude, try this...

=AND(ISNUMBER(SEARCH("PUMP",B3,1)),NOT(ISNUMBER(SEARCH("MOTOR",B3,1))))

replace PUMP and MOTOR with whatever cell references you have

Ha! This seems to work! You have no idea how grateful I am my friend, thank you. I will use it for now and only try to understand it when I have time. Thanks again, I was going to get into trouble if I never found a way to do this.

But just as an extra favour, can anyone explain why my long logic formulae doesn't work? Vlady, I'm aware of it returning zero & non-zero values depending on evaluation, but the logical expressions still stand. So I'm still confused why it deosn't work.

[vlady]

again as i said you are comparing your if with number 1 and text true so it will not work

=IF(AND(IFERROR(SEARCH("PUMP",B3,1),0)=1,IFERROR(SEARCH("MOTOR",B3,1),0)=0),TRUE,FALSE)


the cells used is same as fddibins for comparison and i change your logical comparison to 1/0 ..check results

[Vespasian]

Checked it, doesn't work. True is equivalent to non zero integer on all counts. False is equivalent to zero on all counts.

[witchcraftz]

Vespian

I think I've understood what you want finally but you were not clear on your intial post.

Here is the code that will work for all situations:

In C1 put:

Please Login or Register  to view this content.

I am attaching the example sheet. If any one of the items in B column is "pump" then all the items in C column will show the A column values.

[vlady]

That's the point

True is equivalent to non zero integer on all counts

your original formula in question

=IF(AND(IFERROR(FIND(C$2,$B5,1),FALSE)=TRUE,IFERROR(FIND($C$1,$B5,1),FALSE)=FALSE)=TRUE,$A5,FALSE)

the underline formula above will return 1 or #value error

breaking down your formula
=IF(AND(IFERROR(FIND(C$2,$B5,1),FALSE)=TRUE
assume the underlined return as you say integer 1 if it founds the word.
you compare 1 with TRUE ????

so in other words it will return 1 or #value error (value error then in handled by the iferror)


so the main problem is you are comparing the "integer" with "TRUE" =1=TRUE <---always false

again we are talking about the logical stand as you said

but the logical expressions still stand. So I'm still confused why it deosn't work

[Vespasian]

That's the point



your original formula in question

the underline formula above will return 1 or #value error

breaking down your formula
=IF(AND(IFERROR(FIND(C$2,$B5,1),FALSE)=TRUE
assume the underlined return as you say integer 1 if it founds the word.
you compare 1 with TRUE ????

so in other words it will return 1 or #value error (value error then in handled by the iferror)


so the main problem is you are comparing the "integer" with "TRUE" =1=TRUE <---always false

again we are talking about the logical stand as you said

Ahaaa, got it, that makes perfect sense. So in a computer programming world my logic stands, but in an excel world where 0/Positive integer is actually a number and FALSE/TRUE is text, it doesnt work even though the logic is the same.

Thats why FDibbins formulae worked, because his tested the same logic but this time with testing number to number and not number to text. Thanks everyone for the help, I really appreciate it!!