Want ID to be matched with ID on other sheet and then return First and last name

[sidd0053]

Hi, I'm new here, I did excel in my first year of college and I did learn a lot, but however I have forgotten most of it since I haven't used it since.

Is there a formula I can use that can Match the employee ID on the first worksheet with the employee ID on the 2nd sheet and then return the employees first and last name to the 1st worksheet? I have tried MATCH, INDEX and VLOOKUP and either I'm not using them right or there are errors.

There are 2543 rows of employee ID, and 224 Names.


Any help will be greatly appreciated.

 

[steve the fish]

Sounds like you could use a combination of INDEX/MATCH or VLOOKUP. Show us the formulas you have tried is probably the easiest way to solve it.

 

[Joe4]

Welcome to the Board!

If the employee IDs are unique in the list you are looking up, and the first and last name are to the right of the IDs in the same row in that list, then you should be able to use VLOOKUP.
You would have two VLOOKUP formulas, one for first name and one for last name.
See here for examples and details: https://www.techonthenet.com/excel/formulas/vlookup.php

Some common issues that people come across when using VLOOKUPs:
- The columns that you are matching on must be the same data type. Sometimes people will have IDs entered as Text in one list, and the IDs entered as Numbers in the other list. The values you are matching on MUST be the same type (can only compare Text-to-Text, and Numbers-to-Numbers, cannot compare Text-to-Numbers)
- The values you are matching on MUST match EXACTLY. If there is an extra space at the end of the entries in one list, that is NOT an EXACT match, so it won't work. So you might need to clean-up your data a little first.

See if you can get VLOOKUP to work. If not, post back with details (including the formula you tried), and some simple examples of your data.

 

[sidd0053]

Welcome to the Board!If the employee IDs are unique in the list you are looking up, and the first and last name are to the right of the IDs in the same row in that list, then you should be able to use VLOOKUP.You would have two VLOOKUP formulas, one for first name and one for last name.See here for examples and details: https://www.techonthenet.com/excel/formulas/vlookup.phpSome common issues that people come across when using VLOOKUPs:- The columns that you are matching on must be the same data type. Sometimes people will have IDs entered as Text in one list, and the IDs entered as Numbers in the other list. The values you are matching on MUST be the same type (can only compare Text-to-Text, and Numbers-to-Numbers, cannot compare Text-to-Numbers)- The values you are matching on MUST match EXACTLY. If there is an extra space at the end of the entries in one list, that is NOT an EXACT match, so it won't work. So you might need to clean-up your data a little first.See if you can get VLOOKUP to work. If not, post back with details (including the formula you tried), and some simple examples of your data.

Ill try this out, and ill try and upload some screenshots also. I'm at work so most of the image hosting websites are blocked here.

 

[sidd0053]

Formula I used:
=VLOOKUP(C2,Sheet2!A2:B224,Sheet2!B:B,FALSE)

Sheet 1: From this sheet I'm trying to get the formula to match the ID with Worksheet 2, and then return first and last name.

First Name	Last Name	User ID
#REF!		lxb575
		dxp334
		axb800
		klc134
		axb800
		lxb575
Sheet 2: Once the ID is matched, I want the First name to be returned to worksheet 1. I understand ill have to do the code twice, one for First, and another for the Last name.
First Name Last Name User ID Louise Bourgon, lxb575 Danielle Pion, dxp334 Andree Brassard, axb800 Kerri-Lee Clarke, klc134 Angele Bazinet, axb174 Lucie Forcier, lxf129 Unknown hxk035 Mélanie Sauvé, mxf094 Unknown rxb212 <tbody> </tbody>

<tbody>
</tbody>

 

[Joe4]

Is it possible to reorganize Sheet2 so that the ID is the first column?
You can only use VLOOKUP if the column you are matching in is the first column in your lookup list. Otherwise, you will need to use something else, like INDEX/MATCH.

Also, your third argument in the VLOOKUP formula is incorrect.

[COLOR=#333333]=VLOOKUP(C2,Sheet2!A2:B224,[/COLOR][COLOR=#ff0000]Sheet2!B:B[/COLOR][COLOR=#333333],FALSE)[/COLOR]

It needs to be a number, specifically the column reference of your lookup range (check out the link I provided, as it shows that).

So, if your lookup list had ID in column A, First Name in column B, and Last Name in column C, then your two lookup formulas would look like:
For First Name: =VLOOKUP(C2,Sheet2!A2:C224,2,FALSE)
For Last Name: =VLOOKUP(C2,Sheet2!A2:C224,3,FALSE)

 

[sidd0053]

in the VLOOKUP formula is incorrect.

[COLOR=#333333]=VLOOKUP(C2,Sheet2!A2:B224,[/COLOR][COLOR=#ff0000]Sheet2!B:B[/COLOR][COLOR=#333333],FALSE)[/COLOR]

It needs to be a number, specifically the column reference of your lookup range (check out the link I provided, as it shows that).
QUOTE]

I worked out perfectly! Thank you, I confused the "column Number" with the headers at the top and that explains as to why it was not working. Thanks again. I was struggling with it for a couple hrs lol.

 

[Joe4]

You are welcome! Glad you got it working.

Yes, it is the Index number relative to the range you are looking up.

 

[steve the fish]

You could use an INDEX/MATCH though perhaps. Something like:

=INDEX(Sheet2!A$2:A$100,MATCH($C2,Sheet2!$C$2:$C$100,0))