Employee Lists

EF6

New Member
Joined
Oct 20, 2005
Messages
20
I have been searching for 2 hours and can not find anything to help. I would like to have a worksheet with all employees last name, first name, occupational code, phone number and alternate phone number on one page. On different worksheets will be different facilities where the employee can work. What I would like to be able to do on the different worksheets for the facilities is to type the last name of the employee and the first name, occupational code, phone number and alternate phone number automatically fill in. I have tried vlookup and have failed miserably. Can anyone point me in the right direction? Much thanks in advance.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sheet 1 would have the following information:

A B C D E
1 Last Name First Name OCC Phone Number Alt Number
2 Doe Jane RN 123-4567 987-6543
3 Doe John LPN 456-7896

Sheet 2 (and so on) would have the same column headings:
A B C D E
1 Last Name First Name OCC Phone Number Alt Number
2

(Sheet 1 & 2 are in the same workbook)

What I would like to achieve is that when I type in DOE in A2 on sheet 2, it will automatically fill in the rest of the columns that correspond to Doe in the appropriate places (columns).

Thanks for the interest!
 
Upvote 0
Hello EF6,
For what you're describing vlookup would be perfect except in your example you have a duplicate last name (Doe) and this will present a problem in that vlookup will always return the first one it finds.
Will duplicate last names be an issue in your real list?
 
Upvote 0
There are instances (although not many) were there will be a duplicate last name.

I guess the issue that I am having with vlookup is where to put the formula (as well as how to write it correctly).

Thanks!
 
Upvote 0
Hi,

Try,

In B2 and copied down & across,

=IF(ISNUMBER(MATCH($A2,Sheet1!$A$2:$A$65536,0)),VLOOKUP($A2,Sheet1!$A$2:B$65536,COLUMNS($A$2:A$2)+1,0),"Not Found")

If you want multiple answers to return then,

In F2 on Sheet2,

=COUNTIF(Sheet1!A:A,A2)

In B2 and copied down & across,

=IF(ROWS($A$2:$A2)<=$F$1,INDEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!$A$2:$A$10=$A$2,ROW(Sheet1!$A$2:$A$10)),ROWS($A$2:$A2))),"")

Confirmed with Ctrl+Shift+Enter not with just Enter.

HTH
 
Upvote 0
OK, try this.
Say your original list is in columns A:E of Sheet1. (starting in row2)
In cell B2 of sheet2, write (or paste) in this formula:
(Note - change the E30 in these formulas to the last row of your data in sheet1)
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,2))

In C2, paste this:
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,3))

In D2. . .
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,4))

And in E2. . .
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,5))

Now when you enter a name from columnA of sheet1 into cell A2 of sheet2
all the data from the other columns will display in sheet2.
You can copy these down as far as needed and to the other sheets as needed.
Be aware that lots of vlookup formulas can have a noticeable effect on file size
& calculation time.
 
Upvote 0
Thanks to both HalfAce & Kris. I think that I am doing something wrong however. It is only retuning the information fom the first row in the first row. I have copied the formula down several rows, and it is only returning the correct information for row 2. Is there something that I am doing incorrectly?

E
 
Upvote 0
Hi,

Sheet1
Book1
ABCDE
1Last NameFirst NameOCCPhone NumberAlt Number
2DoeJaneRN123-4567987-6543
3DoeJohnLPN456-7896
Sheet1


Sheet2
Book1
ABCDEF
1Last NameFirst NameOCCPhone NumberAlt Number2
2DoeJaneRN123-4567987-6543
3JohnLPN456-78960
4    
5    
Sheet2


Formula in F2,

=COUNTIF(Sheet1!A:A,A2)

In B2 and copied down & across,

=IF(ROWS($A$2:$A2)<=$F$1,INDEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!$A$2:$A$10=$A$2,ROW(Sheet1!$A$2:$A$10)),ROWS($A$2:$A2))),"")

Confirmed with Ctrl+Shift+Enter

HTH
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,726
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top