Lookup Values from seperate sheets

[ShuDawg]

I am in the process of creating a Phone Roster for my unit. On Sheet1 is their administrative data (Name, Position, Phone Number, etc.). What I am attempting to do is use a formula to find the persons name for that position and place it in the appropriate position and then their phone number below.

Admin!H:H contains the positions
Admin!B:B contains their name
Admin!N:N contains their phone number

I believe the following is what I need =LOOKUP("Operations NCO",Admin!$H:$H,Admin!$B:$B) and it works, but only for the first person on Sheet 2. After that it is random. If there a different formula I need to use to make this work?

When I get the phone number I used =LOOKUP("Operations NCO",Admin!$H:$H,Admin!$N:$N) and the same thing happens. It works for the first person and everything else is incorrect.

A problem is the Admin sheet is listed alphabetically, not by position, so I cant use a specific cell to copy the data. I need the formula to find the position, then find the name and phone number of the person in said position.

[oeldere]

I think you have to use Vlookup for your problem.

Otherwise post your excel file without confidential information.

[ShuDawg]

Attached is an example document with the information.

In this example, sensitive information has been scrubbed but the problem remains.

In the Phone Roster sheet, B15=LOOKUP("Operations NCO",Admin!$E:$E,Admin!$B:$B)which is correct. However the problem I am running into as you can see, is that that formula is not working in many of the example cells. I am unsure why that is. I am not familiar with how to write VLOOKUP to do what I need it to do if that is the proper formula.

[oeldere]

See the green and yellow cell in the attached file.

[protonLeah]

Please Login or Register  to view this content.

Where
PersonnelData refers to: =Admin!$A$5:$F$12
and
DutyPosition refers to: =Admin!$E$5:$E$12

[ShuDawg]

Thank you all so much for your help. Now there is only one more thing. My boss wants just Last names listed.

=CONCATENATE(INDEX(Data,MATCH($L$3,Position,0),3)," ",INDEX(Data,MATCH($L$3,Position,0),1))

Returns rank and full name (CPT DOE JOHN A). How can I change INDEX(Data,MATCH($L$3,Position,0),1)) to return only with the first word in the cell?