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.

HTH

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.