Matching a partial name/nickname to full name via a lookup

[Tigerexcel]

I have a spreadsheet which needs to look up information from another spreadsheet for staff members. The problem is that in 1 file the name may be listed as Norm Brown (all in one field), the other one could have Norman Brown (all in one field). Other examples are Chris Jones and Christopher Jones. It isn't a matter of simply putting in a wildcard in the first argument of a Lookup or Match argument because the first spreadsheet has names anywhere from 3 to 10 characters. I'm sure there must be a way, just can't get it to work at the moment.

 

[Yongle]

Consider adding staff employee number as column A in BOTH workbooks
Column A now provides a reliable lookup making the two inconsistent name fields irrelevant
Is there any reason why that cannot be done?

 

[Aladin Akyurek]

You can try the following which could be applicable in your set up...

Chris Jones	blue				Christopher Jones		blue
Norman Brown	black				Norm Brown		black
	#N/A

In B2 control+shift+enter, not just enter, and copy down:

=IFERROR(VLOOKUP(SUBSTITUTE(A2," ","*"),$F$2:$H$10,3,0),VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH(SUBSTITUTE(IF($F$2:$F$10="","#",$F$2:$F$10)," ","*"),A2),$F$2:$F$10),$F$2:$H$10,3,0))

 

[Tigerexcel]

Consider adding staff employee number as column A in BOTH workbooks
Column A now provides a reliable lookup making the two inconsistent name fields irrelevant
Is there any reason why that cannot be done?

Nice idea but there are over 1000 records so this would take me some time.

 

[Tigerexcel]

You can try the following which could be applicable in your set up...

Chris Jones	blue				Christopher Jones		blue
Norman Brown	black				Norm Brown		black
	#N/A

In B2 control+shift+enter, not just enter, and copy down:

=IFERROR(VLOOKUP(SUBSTITUTE(A2," ","*"),$F$2:$H$10,3,0),VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH(SUBSTITUTE(IF($F$2:$F$10="","#",$F$2:$F$10)," ","*"),A2),$F$2:$F$10),$F$2:$H$10,3,0))

Thanks Aladin, I tried this but it didn't work, what is the significance of the blue and black?

 

[Yongle]

Nice idea but there are over 1000 records so this would take me some time.

 

[Aladin Akyurek]

Thanks Aladin, I tried this but it didn't work, what is the significance of the blue and black?

The proposal effects a look up using VLOOKUP, which requires a table, here located in F2:H10. H2:H10 is the third column of the table and "blue" and "black" are results associated with chris jones and norman brown.

 

[Tigerexcel]

The proposal effects a look up using VLOOKUP, which requires a table, here located in F2:H10. H2:H10 is the third column of the table and "blue" and "black" are results associated with chris jones and norman brown.

I tried it again, this time it worked for the majority of records, it didn't pick up the information for Harry Stiles, where the other field had Harrison Stiles. Still a huge improvement over having to do it manually so really appreciate your input. The formula contains a "#", sign what does that do please?

 

[Aladin Akyurek]

Yep, y of Harry and i of Harrison do not match; other cases like jenny -> Jennifer won't either. Hope the counts of such pairs are very low for dimissing the approach.

# is a filler item for empty or blank cells in the match range of the table, here F2:F10 of F2:H10. If your match range does not contain such entries, the formula can be simplified like this, improving the processing speed:

=IFERROR(VLOOKUP(SUBSTITUTE(A2," ","*"),$F$2:$H$10,3,0),VLOOKUP(LOOKUP(9.99999999999999E+307,SEARCH(SUBSTITUTE($F$2:$F$10," ","*"),A2),$F$2:$F$10),$F$2:$H$10,3,0))