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.
- The first name on sheet2 have abbreviated names or nicknames, I was hoping to do another vlookup with wildcards to sort the first names. Unfortunately there is no other common data between the two fields. I was hoping to narrow it down a bit as my current solution is returning everyone that has the same last name as a creator when only one person of most of those last names are.
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.
