Forum Discussion
Looking to do a rather involved vlookup
I have a report that I have to make between two different data sources that I put into a workbook on separate pages. I want to have text show in a field where the data matches from sheet2. Sheet1 is a complete list of users & Sheet2 has people who are "creators".
Problem is I am having a difficult time using VLOOKUP since data doesn't match identifying people between the two sheets. Sheet1 has names listed "lastname, firstname" and Sheet2 has "firstname lastname". Issue I'm running into is that Sheet2 does not have full first names where Sheet1 does. I added extra columns and put first names in one and last names in another, but can't figure on what formula to use. I was able to use a VLOOKUP with last names but ran into the problem that where first names were different it auto filled for them as well. I'm including pictures to show what Sheet1(Users) & Sheet2 sort of look like. I made an abbreviated sample as the list I'm using for Sheet1 has 2800+ rows and Sheet2 has 4500+ rows (as some are duplicates for creators). I would like results similar to Column E in the 'User' sheet.Sheet2User(Sheet1)
4 Replies
- need to print my excell but it cannot find it.
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.
