CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
dan
dan

Reputation: 3519

Excel formula to find last occurrence of a value in a column (with conditions)

So I basically want to find the index (position) of the last value which meets a certain amount of conditions.

Here is an example of the formula I use to find the FIRST match:

={MATCH(1,(Sheet!$B5:$B50000=$B$3)*(Sheet!$D5:$D50000>$E$3)*(C$8=Sheet!$A5:$A50000)*(Sheet!$E5:$E50000="CALLINBOUND"),0)}

This works fine, I'm finding the first "CALLINBOUND" value in E5:E50000 after checking some other conditions (basically B5:B50000 are IDs, D5:D50000 are hours and A5:A50000 are dates).

Now, let's say I want to find the last "CALLINBOUND" value in that same E5:E50000 range with the same conditions: 

Sheet!$B5:$B50000=$B$3
Sheet!$D5:$D50000>$E$3
C$8=Sheet!$A5:$A50000
Sheet!$E5:$E50000="CALLINBOUND"

How should I proceed?

I found a couple of answers using MAX() and SUMPRODUCT() or LOOKUP() but it only returns the last match in a range without checking for any condition, so I'm kind of stuck. Also, if there is any way to optimize the performance of the above formula, that would be appreciated. The range will eventually grow and it's slow with array formulas.

I may use Access in the future, I guess this would be easier with SQL than with pure Excel formulas.

Thank you!

Upvotes: 0

Views: 3580

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

If you have 2007 or earlier than use this array formula:

=Max(IF((Sheet!$B5:$B50000=$B$3)*(Sheet!$D5:$D50000>$E$3)*(C$8=Sheet!$A5:$A50000)*(Sheet!$E5:$E50000="CALLINBOUND"),Row($5:$50000)))

Being an Array the use Ctrl-Shift-Enter when exiting edit mode.

If you have 2010 or later you could use:

=Aggregate(14,6,Row($5:$50000)/((Sheet!$B5:$B50000=$B$3)*(Sheet!$D5:$D50000>$E$3)*(C$8=Sheet!$A5:$A50000)*(Sheet!$E5:$E50000="CALLINBOUND")),1)

If you have the latest Office 365 or are using the online version then:

=MAXIFS(Row($5:$50000),Sheet!$B5:$B50000,$B$3,Sheet!$D5:$D50000,">" & $E$3,Sheet!$A5:$A50000,C$8,Sheet!$E5:$E50000,"CALLINBOUND")

If you wanted to replace your current formula to find the first then in the first change MAX to MIN. In the Second change 14 to 15. In the Third change MAXIFS to MINIFS

Upvotes: 2

Related Questions