Alternate need for vlookup

[SeniorTom]

The old Roster file had Column "B" = Lastname Column "C" = firstname.
[TABLE="width: 249"]
<tbody>[TR]
[TD]GHIN #[/TD]
[TD]Last Name[/TD]
[TD]First Name [/TD]
[/TR]
[TR]
[TD]1910534[/TD]
[TD]Smith[/TD]
[TD]Bob[/TD]
[/TR]
</tbody>[/TABLE]

The new file has Column "B" = lastname, firstname.
[TABLE="width: 285"]
<tbody>[TR]
[TD]GHIN #[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1910534[/TD]
[TD]Smith, Robert[/TD]
[/TR]
</tbody>[/TABLE]

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