Excel 2013: VLookup for cells that share common characters within cell but are both surrounded by other non-matching text
- by Kylie Z
I am pulling information from 2 different databases. The databases use different naming protocol for the exact same item/specified placement however they always have certain components of the name in common. The length of these names can vary throughout each of the databases (see the pic below) so I don't think counting characters would help. I need a formula (probably a vlookup/match/index of some sort) to pair up the names from the 2nd database name with the 1st database name and then place it in the adjacent column(B2) on sheet1. Until this point I've had to match, copy, and paste the pairs manually from one sheet to the other and it takes FOREVER. Any help would be much appreciated!!!
For example:
Database1 Name in Sheet1,A2:
728x90_Allstate_629930_ALL_JUL_2013_MASSACHUSETTSAUTO_BAN_MSN_ROSMSNAUTOSMASSACHUSETTS_7.2.13
Database2 Name in Sheet2, A13:
BAN_MSN_ROSMSNAUTOSMASSACHUSETTS728X90_728X90_DFA
Common Factors:
"ROSMSNAUTOSMASSACHUSETTS" & "728X90" Therefore A2 and A13 need to pair up
In some cases, Database 1 and 2 will have a common name aspect but sizing will be different. They need to have BOTH aspects in common in order to be paired so I would NOT want the below example to pair up.
Database1 Name in Sheet1,A2:
728x90_Allstate_629930_ALL_JUL_2013_MASSACHUSETTSAUTO_BAN_MSN_ROSMSNAUTOSMASSACHUSETTS_7.2.13
Database2 Name in Sheet2, A12:
BAN_MSN_ROSMSNAUTOSMASSACHUSETTS300X250_300X250_DFA
Common Factor:
Only "ROSMSNAUTOSMASSACHUSETTS" matches.
"728x90" is not equal to "300X250" - Sizing is different so they should not be paired.
