Excel Formula: Return value from array with multiple criteria

Question

Suppose the following series:

I am trying to find the latest non 1 value that precedes the latest 1.

In this case it should return 3 and not 4.

1 being the minimum value I have tried to use MATCH(MIN(range),range,0) and add 1 to get the value I needed, but the minimum function gets stuck on the first occurrence of the minimum.

In this case it should return 3 and not 4. makes no sense to me. — user4039065
– user4039065, Commented Jan 29, 2018 at 10:48
Do you mean precedes in the sense of being at an earlier date (but follows in order of presentation) ? — Tom Sharpe
– Tom Sharpe, Commented Jan 29, 2018 at 11:00

TomJohn · Accepted Answer · 2018-01-29 12:09:06Z

1

Try

=INDEX(B1:P1,1,MATCH(1,(OFFSET(B1:P1,,-1)=1)*(B1:P1>1),0))

Where B1:P1 is your data range. Of course it is an array formula (SHIFT+ENTER).

Hope that helps.

edited Jan 29, 2018 at 12:09

answered Jan 29, 2018 at 11:02

TomJohn

7476 silver badges19 bronze badges

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

Comments

Tom Sharpe · Accepted Answer · 2018-01-29 14:36:27Z

0

I can't see a short and snappy answer to this but here is one suggestion assuming the data starts in column A

=INDEX(2:2,AGGREGATE(15,6,COLUMN(INDEX(2:2,MATCH(1,2:2,0)):INDEX(2:2,MATCH(999,2:2)))/(INDEX(2:2,MATCH(1,2:2,0)):INDEX(2:2,MATCH(999,2:2))>1),1))

If the range didn't start in column A, you would have to subtract the number of the column before the first column of the range from the column number returned by the AGGREGATE to get the correct index value relative to the start of the array e.g. for B2:Z2

=INDEX(B2:Z2,AGGREGATE(15,6,COLUMN(INDEX(B2:Z2,MATCH(1,B2:Z2,0)):INDEX(B2:Z2,MATCH(999,B2:Z2)))/(INDEX(B2:Z2,MATCH(1,B2:Z2,0)):INDEX(B2:Z2,MATCH(999,B2:Z2))>1),1)-COLUMN(A:A))

To be honest it wouldn't be worth using a MATCH to find the last number in the range unless the number of cells in the range was very large, so the formula for B2:Z2 would just be

=INDEX(B2:Z2,AGGREGATE(15,6,COLUMN(INDEX(B2:Z2,MATCH(1,B2:Z2,0)):Z2)/(INDEX(B2:Z2,MATCH(1,B2:Z2,0)):Z2>1),1)-COLUMN(A:A))

Formula starting column A

Formula starting at column B

edited Jan 29, 2018 at 14:36

answered Jan 29, 2018 at 11:33

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

4 Comments

user1627466 Over a year ago

Thanks but it doesn't give the expected result for the following row: 2 2 1 1 3 3 3 2 2 2 2 1 1 2

user1627466 Over a year ago

This should be 3 also, the latest non 1 that precedes (in time) 1. I get a value of 1 from the formula.

user1627466 Over a year ago

Clearly I am doing something wrong, I only replaced 2:2 with the appropriate range.

Tom Sharpe Over a year ago

Sorry for no response, I had to do other things. Will add to my answer.

Collectives™ on Stack Overflow

Excel Formula: Return value from array with multiple criteria

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related