Match Differently Formatted Text Columns

Hi All,

I'm trying to pair up spend info from a source with revenue info that source generated which is many hundreds of rows long. My issue is that the reported Spend Source is in a slightly different format (capital letters, spaces added, etc) vs the revenue source. Wondering if there's a way (maybe some form of VLOOKUP?) that I could match the two if the columns have some form of common text?


Eg. (more info in attached sheet)
Spend Source:
Website One - CoolStuff
$100

Revenue Source:
websiteone-coolstuff
$120

Thanks for any help!

Hi -

You can use SUBSTITUTE to get rid of extra spaces, and then use MATCH to find text that matches. MATCH doesn't care if letters are capitalized or not. Aa = aa according to MATCH. So you could write a little formula like this:

=MATCH(SUBSTITUTE(A2," ",""),$E$2:$E$4,0)

Which will return the row in E2:E4 that matches A2 (WebsiteOne-CoolStuff Note the spaces have been removed by SUBSTITUTE).

The only problem I see with your data is abc123-websitetwo is never easily going to equal Websitetwo. Spaces and letter case is one thing. Deciphering text out of random text will be a problem. If there are only one or two cases where that occurs, you can probably work around it (e.g., find them manually, add a formula in a separate column that uses SUBSTITUTE to get rid of those few specific instances, etc.) If there's a lot of cases with random text, it's going to be a lot more difficult.

Hope this helps.