I have a sheet with current users of a system (only by username) and I have another sheet that is a master list of username associated with employee first name, last name, and employment status. What I want is some formula where from sheet User List with the current list of users pulled from the system that only contains usernames and I want to check that list of usernames against the master list and import the 3 columns of last name, first name, and status to the current user list if there is a match and if not to return an error. I've tried an IF ISERROR line that didn't work and INDEX MATCH MATCH MATCH that didn't work.

Essentially, when any username on sheet 1 "User List" matches a username on sheet 2 "Master List", copy over the values of Last Name, First Name, and Status into columns B, C, and D respectively into sheet 1.

sheet 1 - user list

sheet 2 - master list


2 Answers 2


Datanovice is 100% right. Just use VLOOKUP in the Last Name, First Name and Status rows.

I've only prepared this answer to show you that if there is no match in the master list, AND if you set the 'is_sorted' parameter to FALSE, then VLOOKUP will return an error.

In this case, carrot3 is on the user list, but isn't on the master list.

enter image description here


One single array formula in cell B2 of 'User List' should do the job (i.e., don't copy/drag it anywhere else; it'll do the whole job from that one cell):

=ArrayFormula(IFERROR(VLOOKUP(A2:A,'Master List'!A2:D,{2, 3, 4},FALSE),{"","",""}))

In English: "For every item in Column A from cell 2 down, try to find it in 'Master List.' If it's found, return the array of values from Columns 2, 3 and 4 of that row in 'Master List.' If it's not found (i.e., IFERROR), return an array of three horizontal null cells."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.