Index function

WarfritLive

Board Regular
Joined
Sep 27, 2016
Messages
149
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 NumberLast NameFollow Up Date
2
1
Jane
3
2
Mike
1/25/2017
4
3
Jeff
5
4
Mark
1/30/2017
6
5
RealnameNo Follow Up
7
6

<tbody>
</tbody>
Sheet: Master

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

abhi1687

Board Regular
Joined
Mar 2, 2013
Messages
110
you can use this:
Code:
=IFERROR(AGGREGATE(15,6,(ROW(Master!$A$2:$A$7)/(Master!$C$2:$C$7<=TODAY())),ROWS(Master!$A$2:A2)),"")
 

WarfritLive

Board Regular
Joined
Sep 27, 2016
Messages
149
you can use this:
Code:
=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

Board Regular
Joined
Mar 2, 2013
Messages
110
there you go:
Code:
=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),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,187
Messages
5,594,748
Members
413,930
Latest member
Nela817

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top