index match formula
 

Hi,

I have a roster eg

date 1/1 2/1 3/1

john night day off
andrew day off night
fred off night day


in a cell I would like returned the name of the person who had done a
particular night. eg. who did night shift on 2/1 ie. fred.

I know its a index match, but getting it all wrong or is there another
solution.

andrewm


--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=388302

Hi,

See the attachment.

HTH


+-------------------------------------------------------------------+
|Filename: Andrew.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3610 |
+-------------------------------------------------------------------+

--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=388302

Andrew,

One solution

=INDEX(A2:A4,MATCH("Night",INDIRECT(CHAR(65+MATCH(--"2005-01-02",B1:D1,0))&"
2:"&CHAR(65+MATCH(--"2005-01-02",B1:D1,0))&"4"),0))

change the ranges to suit.

--
HTH

Bob Phillips

"andrewm" wrote in
message ...

Hi,

I have a roster eg

date 1/1 2/1 3/1

john night day off
andrew day off night
fred off night day


in a cell I would like returned the name of the person who had done a
particular night. eg. who did night shift on 2/1 ie. fred.

I know its a index match, but getting it all wrong or is there another
solution.

andrewm


--
andrewm
------------------------------------------------------------------------
andrewm's Profile:
http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=388302

Let A1:D1 house dates, with the label 'date' in A1.
Let A3:D5 house the rest of the data.

In H2 enter:

=INDEX($A$3:$A$5,MATCH($G2,INDEX($B$3:$D$5,0,MATCH ($F2,$B$1:$D$1,1)),0))

where F2 houses a date of interest like 2/1 and G2 a value like "night".

andrewm wrote:
Hi,

I have a roster eg

date 1/1 2/1 3/1

john night day off
andrew day off night
fred off night day


in a cell I would like returned the name of the person who had done a
particular night. eg. who did night shift on 2/1 ie. fred.

I know its a index match, but getting it all wrong or is there another
solution.

andrewm



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.