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:

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

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),"")
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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