Formula for automatically entering manager names when a name is entered into a column

[Sequential21]

I'm working on a data entry spreadsheet.

Basically the process needs to be - Enter Worker's Name in the first column>Manager's name is automatically entered in the second column>Area manager's name is entered in the third

e.g. Joe Smith>David Jones>Mark Johnson

On a separate sheet I've got a list of the names of all of the workers (400 or so) with the managers (around 50) and area managers (10) in separate columns. I need a formula for this first sheet that automatically enters the manager and area manager's names in the adjacent columns when the worker's name is manually entered.

I've attempted using the IF and OR function with no success so far.

 

[SteveO59L]

use vlookup()

 

[oldbrewer]

vlookup is what you want - make a table with employee names in first column, managers in second call it mytable1

make another table with manager names in first column, area manager in second, call it mytable2

enter fred in A1
in B1 =vlookup(A1,mytable1,2)
in C1 =vlookup(B1,mytable2,2)

if needed add if(A1="","",vlookup............

 

[Sequential21]

Thank you it seems to be half-working. It displays the name of one of the managers in the table but not the correct one that's directly next to the worker's name. Any idea why it isn't showing the correct one?

 

[oldbrewer]

copy and paste the table onto here

 

[oldbrewer]

									area mgr
			manager					alan	sue
		fred	alan					colin	ann
		bill	colin
		tom	colin
		dave	alan
		sid	colin
								mytable2
		mytable1
	enter a name
	tom	colin	ann
	colin
	=VLOOKUP(D28,mytable1,2)
	ann
	=VLOOKUP(E28,mytable2,2)

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Sequential21]

Just resolved it by adding FALSE to the end of the VLOOKUP formula.