Forum Discussion
Looking to do a rather involved vlookup
It's not altogether clear what you mean by
ran into the problem that where first names were different it auto filled for them as well.
Unless what you're referring to is what happens when there are two different people with the same last name, since the last name is your variable for the VLOOKUP. IF that's what you mean, there's not a lot you can do since VLOOKUP really relies on the item being looked up being unique.
Which leads to another question: is there something else that is unique (this is where things like ID numbers, Social Security numbers, Membership ID numbers--whatever--come in handy). Is there anything like that associated and on BOTH of your sheets?
If not, I don't think there's a lot you can do. It's not really an issue with VLOOKUP per se (or any other kind of LOOKUP, such as INDEX and MATCH, XLOOKUP, etc) so much as it is a data integrity issue.
Let the VLOOKUP you have do the best it can do, and fill in the rest manually. Then do whatever you can do to get these two sources to be consistent, or to start using a unique ID.
You could use the first letter of the first name/nickname as the comparison...(which would have the same effect as using wildcards. Create a separate column if need be, using LEFT(CellRef,1) to extract the first letter.
Maybe concatenate the last name with the first letter or first two letters of the first name.... to create an almost unique identifier.
If A1 contains the last name, B1 the first
=A1&LEFT(B1,1) or, first first two letters of first name, =A1&LEFT(B1,2)
Use that new text string as the basis for the lookup. Do that whether or not it's nickname or first name....that should eliminate most if not all of the duplicates, in much the same way as a wildcard.