Been at it for a while... still trying to figure it out

All fields marked * are to be modified, while those marked otherwise
are static

I am working on a duty roster table that looks like this:

Name* - Shift* - Mon* - Tue* - Wed* - *etc etc etc
dude1 - shft1- D1 - D1 - D3 <---- let's call these Markers
dude2 - shft1 - D2 - D2 - D2
dude3 - shft2 - N1 - N1 - N2
dude4 - shft3 - D2 - D2 - D1

I have a legend table that says what D1, D2, N1, etc means
like so:

Leg - DAYS* - desc
D1 - 1.0 - Day shift
D2 - 0.5 - Day shift (half)
D3 - 1.0 - Day shift (training)
N1 - 1.0 - Night shift
N2 - 0.5 - Night shift (half)

where DAYS denotes how whether it is a full day or half day shift

Now I have a Headcount table to list the number of people that are
planned for duty:
Shift* - Mon - Tue - Wed
shft1
shft2
shft3

Now in the Headcount table, I want to count how many people are working
on each day who belong to a Shift by first matching their Markers to the
Legend for the number of days, then summing them up.

Sample DESIRED result, based on info provided above:
A - B - C - D
1 Shift* - Mon - Tue - Wed
2 shft1 - 1.5 - 1.5 - 1.5
3 shft2 - 1.0 - 1.0 - 0.5
4 shft3 - 0.5 - 0.5 - 1.0

I'm currently using Array Formulas with the IF function, but I'm having
trouble matching the Markers to the legend table.
This is in cell B2:
{=SUM(IF(<roster shft column range=A2, IF(ISNUMBER(SEARCH("D?",<roster
date range)),VLOOKUP(<roster Mon column,<legend table,2,false),o)))}

This only works halfway cause the VLOOKUP doesn't match all the Markers
in the array with the Legend. Instead uses the value of the first
Match.
So it uses the value for "shft1" on "Mon" which is D1, and counts the
it as 2.0 instead of 1.5 (D1 = 1.0) + (D2 = 0.5).

Help anyone?


--
ronlim
------------------------------------------------------------------------
ronlim's Profile: http://www.excelforum.com/member.php...o&userid=35738
View this thread: http://www.excelforum.com/showthread...hreadid=555209