Hi all,

New to this forum and have spent an hour or so searching for a solution but found nothing that quite works. I'd be very grateful for your help.

I'm creating a rota for my workplace that involves junior workers being allocated to senior workers. Each senior and junior is identified by three initials.

A "job" will be shown on the rota as two cells. The first cell is user entered, and will contain the senior's initials +/- other info. The cell below should extract the senior's initials from the top cell, and print the initials of the correct junior.

The first complicated part:

There are three types of senior.
(1) standard type (ST) - has a single junior
(2) double junior type (DJ) - has two different juniors
(3) group type (GP) - five seniors allocated a single junior

The second complicated part:

The seniors are constant, but the juniors change from time to time. At present, the way I've chosen to match senior to junior is to have a list of them in human-readable format, with the cells containing the junior's initials names as, for example, jnr_jab, where jab are the initial's of that junior's senior. For the group type, the cell containing the junior's initials is labelled jnr_gp. For the double junior type, one will be a jnr_xxx, the other will be a mgr_xxx.

The following table might help:

A B C D E F G H I J K L
1 Work allocation: Standard Seniors "Double junior" Seniors Group Seniors
2 JAB TT (TDC1) JAB DCL PRD, SBL, TLN
3 formula should show "SAB"
jnr_jab:
 SAB Brown
jnr_dcl:
 BLM Mitchell
jnr_gp:
 ATR Roberts
4 DCL (clinic)
mgr_dcl:
 TLP Peters
5 formula should show "BLM, TLP" TDG
snr_dbl:
 PRD, SBL, TLN
6 SBL (night)
jnr_tdg:
 PNL Lampard VTT
7 formula should show "ATR"
jnr_vtt:
 BLS Smith
8 TLN (out)
snr_std:
 JAB, TDG
mgr_vtt:
 PLB Brighouse
9 formula should show "ATR"
10
snr_dbl:
 DCL, VTT

So, taking the example of A3, I think the formula must perform the following steps:
  1. Determine which type of senior is in A2 by doing something like a FIND for each of E8, H10 and K5
  2. If it is a standard senior, do an indirect lookup to return jnr_jab
  3. If it is a double-junior senior, do two indirect lookups to return jnr_xxx & mgr_xxx
  4. If it is a group senior, return jnr_gp

Bear in mind that the juniors (SAB, PNL, BLM, TLP, BLS, PLB, ATR) can't be hardcoded (i.e. their initials must be referenced, not written in to formulae), as they'll change every few months. The seniors (JAB, TDG, DCL, VTT, PRD, SBL, TLN) can be hardcoded if necessary.

So, the help of the community would be enormously appreciated. I look forward very much to your replies!

JB