IF and Wildcards

[longstick]

I've done lots of searching online and on this board and I am still unable to get the solutions that I've found to do what I want them to do.

I need a statement that will compare two cells and see if they match. However, one of the cells will contain the "Maker" and the "Model Number". The second cell will only contain the "Model Number".

Lets use A1 and B1.

A1 will have "Test 123"
B1 will have "123"

I would like the final result of the formula to say True because A1 does contain B1. I need to only use Cell references.

I've tried something like this but I can't seem to figure it out. I've used 1 and 0 to make it simple until I've figured it out.

=if(A1="*"&B1&"*",1,0)

 

[BenMiller]

=if(isnumber(find(b1,a1)),1,0)

 

[Marcelo Branco]

Maybe,

=IF(ISNUMBER(SEARCH(B1,A1)),1,0)

M.

 

[BenMiller]

=if(isnumber(find(b1,a1)),1,0)

Maybe,

=IF(ISNUMBER(SEARCH(B1,A1)),1,0)

M.

Great minds think alike! (and so do we)

 

[longstick]

I think that's what I want. thanks!

Can you break it down and quickly explain why it does what it does?

 

[BenMiller]

FIND looks for cell B1 anywhere in A1. (Marcelo suggested using SEARCH, the difference is that SEARCH is NOT case-sensitive; i.e. it will find Abc in 123aBc, etc.). If it finds the entire B1 anywhere in A1, it will return the character number of the starting point. So 123 in ABC123 would return 4. If it doesn't find it, it will evaluate to an error.

ISNUMBER checks whether an expression evaluates to a number and returns either TRUE or FALSE.

So on second thought, you could trim it down to =ISNUMBER(FIND(B1,A1)) and return either TRUE or FALSE; which 1 and 0 are the Boolean equivalents of, respectively.

 

[T. Valko]

=if(isnumber(find(b1,a1)),1,0)

Maybe,

=IF(ISNUMBER(SEARCH(B1,A1)),1,0)

M.

I've done lots of searching online and on this board and I am still unable to get the solutions that I've found to do what I want them to do.

I need a statement that will compare two cells and see if they match. However, one of the cells will contain the "Maker" and the "Model Number". The second cell will only contain the "Model Number".

Lets use A1 and B1.

A1 will have "Test 123"
B1 will have "123"

I would like the final result of the formula to say True because A1 does contain B1. I need to only use Cell references.

I've tried something like this but I can't seem to figure it out. I've used 1 and 0 to make it simple until I've figured it out.

=if(A1="*"&B1&"*",1,0)

One way:

=COUNT(SEARCH(B1,A1))

 

[Marcelo Branco]

Great minds think alike! (and so do we)

Agree

M.

 

[longstick]

Ok. I've been able to get a simple version work. Now this is the formula that I'd like to have work is.

=IF(ISNUMBER(SEARCH(INDEX('[TOOL_TRACK.xls]TOOLS NORTH'!$E:$F,MATCH(F35,'[TOOL_TRACK.xls]TOOLS NORTH'!$F:$F,0),1),E69,)),"OK","NG")

URYU UL-130MC (Cell "E69")
UL-130MC (Index Formula)

I am receiving an error in the search part of the code. Index brings up "UL-130MC" with no issues. When the search compares the the two I receive "#Value!" which in turns give me "NG".

 

[BenMiller]

Try

=IF(ISNUMBER(SEARCH(E69,INDEX('[TOOL_TRACK.xls]TOOLS NORTH'!$E:$F,MATCH(F35,'[TOOL_TRACK.xls]TOOLS NORTH'!$F:$F,0),1))),"OK","NG")