Thread: Nested/Conditional IF statement

I have a row of cells that each do a wildcard search on a text string to deliver a 1 result if true. the formula is as follows;

=IF(ISNUMBER(SEARCH("*A40",B52)), 1, "")

The problem I have is that 2 other cell formulas clash which are;

=IF(ISNUMBER(SEARCH("*A408",B52)), 1, "")
=IF(ISNUMBER(SEARCH("*A4087",B52)), 1, "")

All formulas when finding A4087 return a 1 when I only want it in the last
The first 2 formulas return a 1 when finding A408 and I only want it in the second.

I have tried using a space i.e. "*A40 " & "*A408 " but then I lose data if words run into the string i.e. A40silver would not pick up the text at all.

My proposed solution is to do a conditional if statement i.e. for the A40 search, if A40 is found check if there is a "" in the other 2 corresponding cells before returning a 1. But I can't get the if statement to work. I am sure it should be possible and fairly straight forward but am really struggling to find the written solution !!!

Are there any takers to help me out please. I can happily mock up something to explain better if required. Oh...and I want to stick with formula and not use VBA on this.

regards

Simon

Can you fathom/describe a logic we can then explain to a formula or a macro that says:

A40silver IS an in-string match to A40
A4087red is top of the range is NOT and in-string match to A40 (which clearly is a match unless we add something)

Pretty much anything I can think of would match both strings.

I was thinking that (using the book 1 as an example) I could write something in B5 (and then drag) like;

IF(C5:D5<>1, THEN IF(ISNUMBER(SEARCH("*A40",A5)), 1, ""))

It obviously doesn't make sense within excel but does that give an expert an idea of where I'm coming from or going wrong?

Apologies - I think you require an answer from me - the search is purely for A40, A408, A4087 but as seperate and unrelated items.

Can you describe a logic that results in A4087 not matching when a search for A40 is conducted. I can't.

SEARCH() would match both.
FIND() would match both.

In VBA, .FIND() would match both...

Unless you can describe a method the causes A40 to NOT match against A4087, I'm not sure how to do it, either.

=LOOKUP(10, SEARCH({"A40","A408","A4087"}, A1))

... will return #N/A, 1, 2, or 3

I'm glad you got a formula that works for you, and checking individually for the values you want to SKIP if matched is a good idea.

I didn't suggest that originally because your sample sheet indicated "and so on, and so on" in the titles...which made me think the value to be added would be more and more variations on the same codes...

This technique is not supportable ad infinitum. But in small groups like this, very manageable!

Here, you can actually reduce the formulas to this, I think:

To get A40 only as a 1 return;
=IF(ISNUMBER(SEARCH("*A4087",A5)),"",IF(ISNUMBER(SEARCH("*A408",A5)),"",IF(ISNUMBER(SEARCH("*A40",A5 )),1,"")))

To get A408 only as a 1 return;
=IF(ISNUMBER(SEARCH("*A4087",A5)),"",IF(ISNUMBER(SEARCH("*A408",A5)),1,""))

To get A4087 only as a 1 return;
=IF(ISNUMBER(SEARCH("*A4087",A5)),1,"")

As the length of the code to find lengthens, the need to "eliminate" the shorter versions disappears. You need to eliminate the longer codes when matching a short one.