EXCEL LOOKUP QUESTIONS 1

[susejdrol]

I am working on a seating plan where I could potentially have as many as two people per seat (1 in the AM and 1 in the PM). I have a data sheet that has colunms for each associate, the seat they sit in, and the shift number that they are on. Then, I have two cells next to each seat number. My goal is to write two fuctions so that it will put the shift number in the first box if someone on a morning shift sits in that seat and the shift number in the second box if someone on the evening shift sits in that seat. If the seat is used for both shifts, there should be a shift number in each box.

Any ideas would be greatly appreciated.

Thanks

CP

 

[SkipVought]

Hi,
What are the fields in your table and what is the criteria for making a seat assignment and where is that data coming from?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[susejdrol]

There is a column for the associate's name, their shift number, and their seat assignment. The function/formula will not need to actually assign seats, it will just need to note if there is someone in the AM and/or PM that is currectly sitting in that seat.

Thanks for the help.

CP

 

[SkipVought]

1. the table of seating assignments sorted by Seat, Shift and named using menu item Insert/Name/Create/Create Names in Top Row...

Seat  Shift  Associate
1     1      Mark
1     2      Al
2     1      Fred
2     2      Jack
3     1      Skip
3     2      John

2. the seating table

Seat Nbr AM     PM
1        Mark   Al
2        Fred   Jack
3        Skip   John

3. the formulas in the AM & PM Columns

=INDEX(Associate,MATCH(Seat_Nbr,Seat,0),1)
=INDEX(Associate,MATCH(Seat_Nbr,Seat,0)+1,1)

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[susejdrol]

Awesome, Thanks

CP