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)

 

[longstick]

Actually I tried that just after I posted my last message but ran out of time before shifts end and wasn't able to post a reply until this morning.

I believe I found my error.

I put an extra , after the Index formula.

=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")

Once this comma was removed the formula worked.

Next question. How do we make this formula work if UL-130MC contains extra text at the end of it. It could be as little as a space or a maybe a couple of letters. Is it possible to search for all or some of the model number? I believe that I don't have a model number less than 5 character (excluding the space or extra letters that may be at the end.)

I have 3000 rows of data and with around 300 different models. Changing them all to have the same "formatting" would be a long task. What doesn't change is the "root" model number.

 

[Aladin Akyurek]

Actually I tried that just after I posted my last message but ran out of time before shifts end and wasn't able to post a reply until this morning.

I believe I found my error.

I put an extra , after the Index formula.

=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")

Once this comma was removed the formula worked.

Next question. How do we make this formula work if UL-130MC contains extra text at the end of it. It could be as little as a space or a maybe a couple of letters. Is it possible to search for all or some of the model number? I believe that I don't have a model number less than 5 character (excluding the space or extra letters that may be at the end.)

I have 3000 rows of data and with around 300 different models. Changing them all to have the same "formatting" would be a long task. What doesn't change is the "root" model number.

If it's the INDEX result which has extraneous bit, check whether this...

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

satisfy?

 

[longstick]

Aladin,

Not quite sure that I'm able to use that. Tool model names range from 5-20 character.

 

[Aladin Akyurek]

Aladin,

Not quite sure that I'm able to use that. Tool model names range from 5-20 character.

If not, you could try fuzzy matching:

http://www.mrexcel.com/forum/showthread.php?t=195635

instead of SEARCH.

 

[longstick]

I thought I would write a follow up and let the contributors know where I stand with the formulas I was seeking. I've managed to find something that worked.

It uses the ISNUMBER and SEARCH options that were listed earlier in the thread.
I was looking to find a way to confirm "123" was in "Test 123". But, sometimes "123" is longer or shorter and might have some strange letters at the end that aren't required for what I need but required for someone else.

The solution was to look at backwards. What never changes is the Full Maker and Model name "Test 123". Sometimes is "Test 12345" or "Example 123" or "Example 12345". This list with just the model name sometimes has extra "junk" at the end. There is never extra letters after the model number on the Maker and Model name list ("123", "12345").

I used RIGHT and LEN to remove the Maker ("Test" and "Example") to leave me with the bare model number. Next I took the bare model number and looked for it inside the model number with the extra letters and numbers. It worked. I would add more IF statements for every Maker and change the LEFT(E70,4)="Test" to LEFT(E70,4)="Exam" and so on.

An example is listed below.

IF(LEFT(E70,4)="Test",IF(ISNUMBER(SEARCH(RIGHT(E70,LEN(E70)-5),INDEX('[TOOL.xls]SHEET1'!$E$1:$F$3800,MATCH($F$35,'[TOOL.xls]SHEET1'!$F$1:$F$3800,0),1))),"OK","NG"),""))

Thanks for all that helped!

 

[Aladin Akyurek]

I thought I would write a follow up and let the contributors know where I stand with the formulas I was seeking. I've managed to find something that worked.

It uses the ISNUMBER and SEARCH options that were listed earlier in the thread.
I was looking to find a way to confirm "123" was in "Test 123". But, sometimes "123" is longer or shorter and might have some strange letters at the end that aren't required for what I need but required for someone else.

The solution was to look at backwards. What never changes is the Full Maker and Model name "Test 123". Sometimes is "Test 12345" or "Example 123" or "Example 12345". This list with just the model name sometimes has extra "junk" at the end. There is never extra letters after the model number on the Maker and Model name list ("123", "12345").

I used RIGHT and LEN to remove the Maker ("Test" and "Example") to leave me with the bare model number. Next I took the bare model number and looked for it inside the model number with the extra letters and numbers. It worked. I would add more IF statements for every Maker and change the LEFT(E70,4)="Test" to LEFT(E70,4)="Exam" and so on.

An example is listed below.

IF(LEFT(E70,4)="Test",IF(ISNUMBER(SEARCH(RIGHT(E70,LEN(E70)-5),INDEX('[TOOL.xls]SHEET1'!$E$1:$F$3800,MATCH($F$35,'[TOOL.xls]SHEET1'!$F$1:$F$3800,0),1))),"OK","NG"),""))

Thanks for all that helped!

Does...

TRIM(REPLACE(E70,1,FIND(" ",E70),""))

help?

By the way, could you give some examples of the junk bit that occurs?