Vlookup type thingy

[JockW]

Creating a internal phone book for a company.
Sheet 1 has the following details:
Division
Department A
employee 1 123456
employee X 123457
employee 3 123458

Department B
employee 2 987654
employee Y 987653
employee Z 987652
etc.

Sheet 2 is an alphabetical listing of all employees, regardless of which
dept. they work in.
My question is how can I get excel to automatically add the section they
work in after the other details ie:
employee 1 123456, Department A
employee 2 987654, Department B
employee 3 123458, Department A
employee X 123457, Department A
etc.

 

[T. Valko]

how can I get excel to automatically add the section they work in

I'm assuming that section is the same as department.

Try this:

Sheet2 A1 = employee Y

=LOOKUP(2,1/SEARCH("department",Sheet1!A1:INDEX(Sheet1!A1:A15,MATCH(A1,Sheet1!A1:A15,0))),Sheet1!A1:INDEX(Sheet1!A1:A15,MATCH(A1,Sheet1!A1:A15,0)))

Result = Department B

 

[JockW]

I oversimplified the issue a bit: Departments A & B are in reality Admin,
Finance, Sales, Support and so on.
After the last name/phone number entry for every department there is an
empty row. Can the function 'work out' where this empty row is and 'look up'
to find the row above the first phone number entry as this is where the dept.
name will be?

 

[T. Valko]

Departments A & B are in reality Admin, Finance, Sales, Support

Yeah, I kind of figured that would be the case!

Ok, this formula requires that there be an empty cell/row above where your
data starts. Let's use this sample:

...........A...
1..............
2.....Sales
3.....Jones
4.....Smith
5............
6....Admin
7....Brown
8....Lee
9....Adams

C2 = lookup value = Lee

Array entered** :

=INDEX(A1:A9,MAX(IF(A1:INDEX(A1:A9,MATCH(C2,A1:A9,0))="",ROW(A1:INDEX(A1:A9,MATCH(C2,A1:A9,0)))-ROW(A1)+1))+1)

result = Admin

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[JockW]

Absolutely brilliant - great stuff, thanks very much.
Gold star for you

 

[T. Valko]

You're welcome. Thanks for the feedback!