Need to fill user id and name from range of cells

Occasional Contributor

I have an excel sheet with over 580K rows of data, it is of user logins, and it gives the username, but not necessarily the full name and it does not give the employee id. See below picture

lordbyronxxiv_0-1649442662215.png

what I want to do is have the ability to all the records on the left column, and if there is a matching record on the right with all the user info then populate the columns on the left with that name and employee id. since there are duplicate user entries, this is where I am having difficulty, is there a way to do a check, if the username exists in the right column then populate the data. in the past I have used a formula to find out if the record exists, IF(Countif("static range","cell#),"True","False) however that does not let me do any sort of copy paste. Any help is appreciated.

4 Replies

@lordbyronxxiv 

=VLOOKUP($A1,$F$1:$H$3,COLUMN(B:B),FALSE)

Is this what you are looking for? Enter the formula with ctlr+shift+enter if you don't work with Office365 or 2021.

Thank you for that, unfortunately I forgot that there is actually no common unique field in both tables, the customers table does not have a field for userID, so I may end up having to do it manually, the customers table only has userID and Full Name, and the logging table has the UserName. Thanks anyway

@lordbyronxxiv 

=IF(A1="mpratt",$G$3,IF(A1="jsmith",$G$1,IF(A1="mgross",$G$2)))
=IF(A1="mpratt",$H$3,IF(A1="jsmith",$H$1,IF(A1="mgross",$H$2)))

Maybe these formula can help you.

@lordbyronxxiv Since you mentioned to work with 580 thousand rows of data, and assuming that you try to match more than just a few customers, perhaps fuzzy matching in Power Query can be used to match (most of) the ID's with the full names. See attached file.