Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help With MATCH & OFFSET | Excel Worksheet Functions | |||
Need help on index and match function | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Vlookup, index, match? | Excel Worksheet Functions |