CODEWITHSUNDEEP
excel
Eric
Eric

Reputation: 3792

Excel Index match with multiple criteria using dates and numbers

Given the following spreadsheet I would like to find the first date after 5/16/2015 that also has a Payment amount > 0. So a Next Payment Date of "7/1/2015" is what I'm looking for.

enter image description here

I tried the following formula: =INDEX(A4:A10,MATCH($E$1,$A$4:$A$10,1)+ 1) but that gives me 6/1/2015 since it doesn't check if the payment is > 0. I have tried various ways of adding the second criteria but haven't been able to get it to work.

Thanks,

Upvotes: 0

Views: 726

Answers (3)

Scott Craner
Scott Craner

Reputation: 152450

Here is another Formula, In E2:

=SMALL(IF(A4:A9>$E$1,IF(B4:B9>0,A4:A9)),1)

It is an array formula and must be confirmed by Ctrl-Shift-Enter

enter image description here

This will find the first date after the date in E1 that is not 0 regardless of sort order.

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36780

Use this array formula.

press CTRL+SHIFT+ENTER to evaluate it.

=INDEX(A4:A10,SMALL(IF(A4:A9>E1,IF(B4:B9>0,ROW(A4:A9)-ROW($A$3))),ROWS($A$4:$A4)))

enter image description here

Upvotes: 1

user4039065
user4039065

Reputation:

In E3 (.. F2?) as,

=INDEX(A:A, AGGREGATE(15, 6, ROW(A4:INDEX(A:A, MATCH(1E+99,A:A )))/((A4:INDEX(A:A, MATCH(1E+99,A:A ))>E2)*(B4:INDEX(B:B, MATCH(1E+99,A:A ))>0)), 1))

You will have to format as a date. The initial result will be 42186.

Upvotes: 1

Related Questions