Index function

[WarfritLive]

Hey guys,

Excel 2013 32 bit

	A​	B​	C​	D​	E​
1​
2​		Client Follow-Up​
3​
4​
5​		The Following Clients need to be followed up with:
6​		PIL #
7​		1​
8​		FALSE​
9​		3​
10​		FALSE​
11​		5​
12​		FALSE​
13​		FALSE​

<tbody>
</tbody>

Sheet: Client Follow-Up

<tbody>
</tbody>

I'm trying to determine which client ID's need to be followed up with based on a date column. I can pull up the actual information, I just need need to condense it so the numbers appear with no gaps. I'm pretty sure I need to use INDEX(), but I'm blanking on how.

The current (wrong) formula in B7 is:
=IF(AND(Master!O2<=TODAY(),Master!B2<>""),Master!A2)
B8 is:
=IF(AND(Master!O3<=TODAY(),Master!B3<>""),Master!A3)
B9 is:
=IF(AND(Master!O4<=TODAY(),Master!B4<>""),Master!A4)
And so on.

I'm pretty sure I need to use INDEX(), but I'm blanking on how.
Instead of 1,FALSE,3,FALSE,5,etc I need range B7:B100 to say 1,3,5,etc to the end.

Thank you in advance

EDIT: Here's a snapshot of the 'Master' sheet if you need it:
Excel 2013 32 bit

	A​	B​	O​
1​	PIL Number	Last Name	Follow Up Date
2​	1​	Jane
3​	2​	Mike	1/25/2017​
4​	3​	Jeff
5​	4​	Mark	1/30/2017​
6​	5​	Realname	No Follow Up
7​	6​

<tbody>
</tbody>

Sheet: Master

<tbody>
</tbody>

 

[arthurbr]

Why not use a filter?

 

[abhi1687]

you can use this:

=IFERROR(AGGREGATE(15,6,(ROW(Master!$A$2:$A$7)/(Master!$C$2:$C$7<=TODAY())),ROWS(Master!$A$2:A2)),"")

 

[WarfritLive]

Why not use a filter?

Because this column is ten thousand rows long and has other formulas dependant on the position of its contents.

you can use this:

=IFERROR(AGGREGATE(15,6,(ROW(Master!$A$2:$A$7)/(Master!$C$2:$C$7<=TODAY())),ROWS(Master!$A$2:A2)),"")

Thank you very much for this

 

[WarfritLive]

you can use this:

=IFERROR(AGGREGATE(15,6,(ROW(Master!$A$2:$A$7)/(Master!$C$2:$C$7<=TODAY())),ROWS(Master!$A$2:A2)),"")

Okay, your formula worked well for me but I've since decided to add a second criteria to it and I apparently don't understand the aggregate function well enough to jam it in there. I've tried AND() among many others.

The first criteria that's already in there is Master!$C$2:$C$7<=TODAY()
The second needs to be
Master!$K$2:$K$7=$H$7

 

[abhi1687]

there you go:

=IFERROR(INDEX(Master!$A$2:$A$7,AGGREGATE(15,6,(ROW(Master!$A$2:$A$7)/((Master!$C$2:$C$7<=TODAY())*(Master!$K$2:$K$7=$H$7))),ROWS(Master!$A$2:B2))-ROW(Master!$A$2)+1),"")