Complex matching with indirect lookup based on partial string match

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

Are jnr_jab, etc defined named ranges?

Is an alternative sheet layout an option? Your layout doesn't look 'formula friendly' and given that you're using excel 2003, some of the limitations and absense of some of the newer functions could make solutions increasingly more difficult to find.

Hi jason.b75,

Thanks for your reply. I'm actually using LibreOffice for Mac, which I think has at least a full set of Excel 2007 formulas. Sorry about the wrong info in my profile - updated now.

Yes, jnr_jab etc in the example are named ranges.

I appreciate your suggestion for an alternative layout, but I'm trying to adapt our existing rota without making it too difficult, as it's been this way for a long time.

I've attached a screenshot of the full weekly sheet FYI, with some dummy data filled in.

Screen Shot 2012-08-17 at 12.45.03.png

Thanks again for your time.

JB

Just looking at the problem again from a different angle, see how this goes based on your first sample table.

=CHOOSE(MIN(MATCH(LEFT(A2,3),{"jab","tdg","dcl","vtt","prd","sbl","tln"},0),5),jnr_jab,jnr_tdg,jnr_dcl&", "&mgr_dcl,jnr_vtt&", "&mgr_vtt,jnr_gp)

My original thoughts of things being close to impossible were based on having to find the senior in the table first, but given the limited number of senoirs and the use of named ranges, I think this should work.

jason.b75,

This worked first time. Then I spent 10 minutes breaking it down to work out what you've done.

That is an inspired and elegant solution, thank you.

JB