index match formula

[andrewm]

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

 

[Krishnakumar]

Hi,

See the attachment.

HT

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

 

[Bob Phillips]

Andrew,

One solution

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

change the ranges to suit.

 

[Aladin Akyurek]

Let A11 house dates, with the label 'date' in A1.
Let A35 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".

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.