Excel Formula using Index/Match

Question

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))

FWIW: The sum is not needed, as far as I can tell. You should get the same answer without it. — Scott Craner
– Scott Craner, Commented May 5, 2017 at 13:45

Scott Craner · Accepted Answer · 2017-05-05 13:57:19Z

1

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.

edited May 5, 2017 at 13:57

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

answered May 5, 2017 at 13:51

Jorvik77

3521 silver badge15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Craner · Accepted Answer · 2017-05-05 18:01:36Z

0

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.

edited May 5, 2017 at 18:01

answered May 5, 2017 at 13:54

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

8 Comments

Hannah Sawyer Over a year ago

Thank you Scott. It is not always in ascending order. The formula should be pulling the date of the release. I think this formula is close but it is still saying not released when the release is right below it. Maybe another piece of logic needs to be added Remark does not start with D?

Scott Craner Over a year ago

Did you enter the formula with Ctrl-Shift-Enter?

Hannah Sawyer Over a year ago

I did, it's saying "not released".

Scott Craner Over a year ago

Also this formula was designed based on yours, it is to be entered first into row 13. then copy/dragged up and down. What is the formula in F2?

Scott Craner Over a year ago

My bad, I had the > wrong should be <. See edit @HannahSawyer

|

Collectives™ on Stack Overflow

Excel Formula using Index/Match

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related