Index function

WarfritLive

Board Regular
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.

EDIT: Here's a snapshot of the 'Master' sheet if you need it:
Excel 2013 32 bit
A
B
O
1
2
1
Jane
3
2
Mike
1/25/2017
4
3
Jeff
5
4
Mark
1/30/2017
6
5
7
6

<tbody>
</tbody>
 Sheet: Master

<tbody>
</tbody>

Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

arthurbr

Well-known Member
Why not use a filter?

abhi1687

Board Regular
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
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:
Code:
``=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

Board Regular
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
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),"")``

Replies
0
Views
71
Replies
0
Views
213
Replies
0
Views
60
Replies
1
Views
419
Replies
3
Views
232

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,892
Messages
5,834,268
Members
430,273
Latest member
Windrunner

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.

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

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