Data matching formula needed!

[Rohan]

Hi all, this is my first post.

I have 2 sets of data of computer models, and need find if the exact model in one column can be found (in full or in part) in another column.

Vlookup won't do as it only gives me results if an exact match is found.

So, if I have 'S391' in one column and 'S391-999' and 'S391-111' in the other column, I want that to return a positive match.

Hopefully this is simple for you Excel wizards out there, and you're help is most appreciated!!

Example attached, two tabs.

Thanks,

Rohan

[alvin-chung]

Hi Rohan, try this array formula in B2 and copy across B14:

Array Formula (CTRL+SHIFT+ENTER)

{=IF(SUM(IF(ISERROR(SEARCH(A2,'all data'!$A$10:$A$25)),0,SEARCH(A2,'all data'!$A$10:$A$25)))>0,"Y","")}

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Richard Buttrey]

Hi,

One way

=MATCH("*"&A2&"*",'all data'!$A$1:$A$100,0)

this will return the row number that contains the match

[alvin-chung]

Brilliant Richard! Totally forgotten about wildcard...

Hi Rohan, in case you would like to hide the #N/A you can 'touched up' the formula as follow:

=IFERROR(MATCH("*"&A2&"*",'all data'!$A$1:$A$100,0),"")

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Rohan]

Hi guys,

Thanks for your answers.

I tried pasting both formulas into my data but neither worked...

Would it be possible for you to paste in to my file (attached) to show the result? I'm sure I'm doing something obvious incorrectly, but can't seem to make it work!

Thanks guys, really appreciated if you can help.

Rohan

[alvin-chung]

Perhaps we have different regional settings, try replace all commas with semicolons in those formulas working for you?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Rohan]

Hi Alvin,

Changing to semicolons didn't make any difference, I'm afraid.

When I paste {=IF(SUM(IF(ISERROR(SEARCH(A2,'all data'!$A$10:$A$25)),0,SEARCH(A2,'all data'!$A$10:$A$25)))>0,"Y","")}
it just shows the formula as the text, above, not the results.

[alvin-chung]

Ah ok, the formula that I put up in post#2 was array formula. Just copy everything except for the curly bracket, and press ctrl+shift+enter (instead of enter) and the curly bracket will appear (indicating that this is an array formula).

ps: for efficiency wise, use Richard solution instead



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Rohan]

OK, so I tried it and it returns a Y for the first cell matched (awesome!) but when I dragged it down, all the other cells are blank. However, there should be matches...

Nearly there... Thanks!

[alvin-chung]

I don't see other matched from your attachment, perhaps your actual file having much larger data range to be matched?
If that's the case, remember to modify the formula to your search range.



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Rohan]

It appears to be working now!

Thank you both!

[alvin-chung]

You're welcome, glad to hear that you finally got it!



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin