CODEWITHSUNDEEP
excelif-statementindexingmatch
Hannah Sawyer
Hannah Sawyer

Reputation: 33

Excel Formula using Index/Match

I'm working with an activity log of orders. My goal is to find the number of orders that were denied, then eventually released. A order was denied if the remark starts with "D", any other remark is a release.

If Remark starts with "D" Match order # in Table with Date greater than Date of "D" remark, return date of release.

This is the formula I'm using but I'm missing the date logic, it is returning the first order # match. 

=SUM(IF(LEFT(C13,1)="D",INDEX($A$2:$E$2305,MATCH(E13,$E$2:$E$2305,0),4),1))

enter image description here

Upvotes: 2

Views: 103

Answers (2)

Scott Craner
Scott Craner

Reputation: 152650

If your data is not always sorted on ascending date, use this array formula:

=IF(LEFT(C13,1)="D",INDEX($D$2:$D$2305,MATCH(1,(E13=$E$2:$E$2305)*(D13<$D$2:$D$2305),0)),1)

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

This formula will show an error if no matches are found, the order was never released to deal with that error we can do this:

=IF(LEFT(C13,1)="D",IFERROR(INDEX($D$2:$D$2305,MATCH(1,(E13=$E$2:$E$2305)*(D13<$D$2:$D$2305),0)),"Not Released"),1)

Now if the order was never released then you will get Not Released You can change that to anything you want.

Upvotes: 0

Jorvik77
Jorvik77

Reputation: 352

If the eventual release date will always be below the denied then setting the index match to start from the next row should fix it. With an image I cannot cut and paste the data to check.

for the row highlighted:

=IF(LEFT(C13,1)="D",INDEX(A14:$E$2305,MATCH(E13,E14:$E$2305,0),4),1)

This does assume that the denial will happen only once.

Upvotes: 1

Related Questions