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.