Another INDEX question

[Roghaltz]

Hi All,
Here is a problem I am wrestling with. We have many sales contests per year (each uniquely identified by the DateEventID column).

Prizes are always awarded to the top 2 salespeople (see the Rank column). At management's discretion prizes can also awarded to one or more salespeople who exhibit extraordinary effort (see the "Y" in the Effort column for winners). There can be between 0 and 2 Effort winners. There are always between 6 and 15 salespeople per contest.

The Prelim column captures the salesperson if they finished in the top 2 OR received an Effort award.

The goal is the OUTPUT column. Namely, at the beginning of each new event capture the salespeople who either finished Top 2 or received an Effort award in the top two to four rows of each event. They can be in any order.

I have been fooling around with INDEX to remove the blanks to go from Prelim to OUTPUT, but can't make it work. There are many events so I'd rather not created OUTPUT by hand.

Excel 2010

	A	B	C	D	E	F	G	H
1	Date	Salesperson	EventType	DateEventID	Rank	Effort	Prelim	OUTPUT
2	Oct-15	a1	a	Oct-15-a	1		a1	a1
3	Oct-15	a2	a	Oct-15-a	5	Y	a2	a2
4	Oct-15	a3	a	Oct-15-a	3			a4
5	Oct-15	a4	a	Oct-15-a	2		a4
6	Oct-15	a5	a	Oct-15-a	4
7	Oct-15	a6	a	Oct-15-a	6
8	Nov-15	a1	b	Nov-15-b	2		a1	a1
9	Nov-15	a2	b	Nov-15-b	4			a3
10	Nov-15	a3	b	Nov-15-b	1		a3	a5
11	Nov-15	a4	b	Nov-15-b	3			a6
12	Nov-15	a5	b	Nov-15-b	5	Y	a5
13	Nov-15	a6	b	Nov-15-b	6	Y	a6
14	Nov-15	a7	b	Nov-15-b	8
15	Nov-15	a8	b	Nov-15-b	7
16	Nov-15	a1	a	Nov-15-a	8			a3
17	Nov-15	a2	a	Nov-15-a	4			a7
18	Nov-15	a3	a	Nov-15-a	2		a3
19	Nov-15	a4	a	Nov-15-a	3
20	Nov-15	a5	a	Nov-15-a	5
21	Nov-15	a6	a	Nov-15-a	6
22	Nov-15	a7	a	Nov-15-a	1		a7

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

Sheet1
​

Many thanks in advance.

 

[DILIPandey]

Okay. so you need column G's value (without blank) in column H at each change of column D (DateEventID).. correct ?



Regards,
DILIPandey

 

[DILIPandey]

Based on my post#2, here is a solution:-

Enter below formula in H2 with key combination ctrl shift enter instead of just enter.

=IFERROR(INDEX($G$1:$G$22,SMALL(IF(($D$2:$D$22=$D2)*($G$2:$G$22<>""),ROW($G$2:$G$22),""),COUNTIF($D$2:$D2,$D2))),"")

and drag down:-



Regards,
DILIPandey

 

[Roghaltz]

PERFECT! Thank you!

 

[DILIPandey]

You are welcome.. cheers



Regards,
DILIPandey