Using Index and Match, returning duplicates.

[monolithicjoe]

Hello,

I am attempting to auto populate a list based on times. I am using this formula and it works great if the shift times are different =INDEX(Sheet1!A:A,MATCH("XXXXXX",Sheet1!C:C, 0))

However, when they are the same, i.e =INDEX(Sheet1!A:A,MATCH("7:00A - 3:30P",Sheet1!C:C, 0)) it will obviously fill in the first name it finds.

Also the reason why all of rows A and C are selected is because the names and times do not always appear in the same row.

Example:

Doe, John		7:00A - 3:30P
Smith, John		7:00A - 3:30P
Rodger, John		7:00A - 3:30P

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Will show as:

Doe, John

Doe, John

Doe, John But would like: Doe, John Smith, John Rodger, John <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[jorismoerings]

Hi,

try something like this:

Book1
	A	B	C	D	E
1	Doe, John	7:00A - 3:30P			7:00A - 3:30P
2	Smith, John	7:00A - 3:30P			Doe, John
3	Rodger, John	7:00A - 3:30P			Smith, John
4					Rodger, John
5
Sheet1

Cell Formulas
Range	Formula
E2	{=IF(ISERROR(INDEX($A$1:$A$3,SMALL(IF($B$1:$B$3=$E$1,ROW($A$1:$A$3)),ROW(1:1)),1)),"",INDEX($A$1:$A$3,SMALL(IF($B$1:$B$3=$E$1,ROW($A$1:$A$3)),ROW(1:1)),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[monolithicjoe]

Hi,

try something like this:

	A	B	C	D	E
1	Doe, John	7:00A - 3:30P			7:00A - 3:30P
2	Smith, John	7:00A - 3:30P			Doe, John
3	Rodger, John	7:00A - 3:30P			Smith, John
4					Rodger, John
5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula E2 {=IF(ISERROR(INDEX($A$1:$A$3,SMALL(IF($B$1:$B$3=$E$1,ROW($A$1:$A$3)),ROW(1:1)),1)),"",INDEX($A$1:$A$3,SMALL(IF($B$1:$B$3=$E$1,ROW($A$1:$A$3)),ROW(1:1)),1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks for the help! But in order to apply this for my needs I would need it to read off one sheet so it fills into another. I tried to edit it so it references the data in Sheet1 but it does not fill in anything.

 

[jorismoerings]

look at this:

Book1
	A
1	7:00A - 3:30P
2	Doe, John
3	Smith, John
4	Rodger, John
Sheet2

Cell Formulas
Range	Formula
A2	{=IF(ISERROR(INDEX(Sheet1!$A$1:$A$3,SMALL(IF(Sheet1!$B$1:$B$3=$A$1,ROW(Sheet1!$A$1:$A$3)),ROW($A1:$A1)),1)),"",INDEX(Sheet1!$A$1:$A$3,SMALL(IF(Sheet1!$B$1:$B$3=$A$1,ROW(Sheet1!$A$1:$A$3)),ROW($A1:$A1)),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Sheet1 (data)

Row\Col	A​	B​
1​
2​	Doe, John	7:00A - 3:30P
3​
4​	Smith, John	7:00A - 3:30P
5​	Rodger, John	7:00A - 3:30P
6​	Del Monte, Linda	9:00A - 4:30P
7​	​	​

Sheet2 (processing)

Row\Col	A​
1​	7:00A - 3:30P
2​	3​
3​
4​	Doe, John
5​	Smith, John
6​	Rodger, John
7​

In A2 just enter:

=COUNTIFS(Sheet1!$B$2:$B$5,A$1)

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",INDEX(Sheet1!$A$2:$A$5,SMALL(IF(Sheet1!$B$2:$B$5=$A$1,ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS($A$4:A4))))