Forum Discussion
Index Match issues.
I have sever databases that show all employees ID's, which i use as the key to matching and analyzing all of my data. I have come across a database that is 3rd party, but I need a lot of the information provided from their database and they DO NOT show a employee ID, AND their names are NOT spelt the same as my 1st party data in Table 2.
Table 1 is the 3rd party database, and you will see that the names have differences than our 1st party data. Small things like missing a period after middle initial, First names not complete, or full names not listed in the same manner. This list is thousands of rows long, I just used this as an example. Just mentioning this because there are a lot of similar errors in all rows.
What I need is the Employee IDs in my 3rd party Data (Column B in Table 1), so that I can put the data into my analytical tables to be analyzed in other programs. I have tried index match, and have not had luck in using 1 or -1 as the match criteria. It gives me the same badge numbers for similar names in both scenarios.
I use VBA, and Functions well, so any type of feedback would be helpful in either platform. Thank you all for your time.
I'd start by inserting a new column in column B.
Enter the following formula in B2 and fill down:
=SUBSTITUTE(A2,".","")Then use column B instead of column A in VLOOKUP or XLOOKUP formulas in column C to retrieve the ID from what are now columns E/F.
Check how many IDs are missing. If it's a small number, I'd look them up manually, but if there are many, we might look at methods to get at least part of those.
I'd start by inserting a new column in column B.
Enter the following formula in B2 and fill down:
=SUBSTITUTE(A2,".","")Then use column B instead of column A in VLOOKUP or XLOOKUP formulas in column C to retrieve the ID from what are now columns E/F.
Check how many IDs are missing. If it's a small number, I'd look them up manually, but if there are many, we might look at methods to get at least part of those.
- Thank you! This ended up inspiring me to break down the names in both databases through the power query, and i was then able to match everything based on Last name, First name, and Middle Initial, which worked very well. I appreciate your help!
