Alternate need for vlookup

[SeniorTom]

The old Roster file had Column "B" = Lastname Column "C" = firstname.

GHIN #	Last Name	First Name
1910534	Smith	Bob

<tbody>
</tbody>

The new file has Column "B" = lastname, firstname.

GHIN #	Name
1910534	Smith, Robert

<tbody>
</tbody>

We want to use the new format so we don't have to maintain two roster file and the new format is not flexable, ... so I need to be.

There are too many versions of "lastname, firstname" eg: Smith, Bob - Smith, Robert - Smith, Bobby, etc. So I asked for last name only

My old code to get their email address was:

Range(emailColumn & FirstRow & ":" & emailColumn & LastRow).Formula = "=VLOOKUP(B2,'" & RosterFileName & "'!roster,5,FALSE)"

I've looked at split and match but have not hit on a clean solution. Any thoughts would be appreciated.

 

[mole999]

the issue with VLOOKUP is that SMITH will return the first SMITH, also is is reliant on sort order

INDEX MATCH would be a better choice, but I also think some Fuzzy logic might be needed and not sure how to implement that. You also seem to have a unique identifier so many work that in

your old data highligh duplicates on that and see what is a real issue

 

[SeniorTom]

Smith was a poor example, I have a work around for my 2 Smiths and 2 Dunns. The match returns the row number when used as a function on the spreadsheet, But I'm having some problems using it in VBA ... still at it

Thanks for your feedback