Forum Discussion

Copper Contributor

Jun 28, 2022

Solved

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 informa...

excel

Formulas and Functions

Macros and VBA

HansVogelaar
Jun 28, 2022
ADGToreUp

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.

HansVogelaar

MVP

Jun 28, 2022

ADGToreUp

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.

ADGToreUp

Copper Contributor

Jun 28, 2022

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!