CODEWITHSUNDEEP
excelexcel-formula
user1627466
user1627466

Reputation: 423

Excel Formula: Return value from array with multiple criteria

Suppose the following series: enter image description here

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.

Upvotes: 0

Views: 77

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

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

enter image description here

Formula starting at column B

enter image description here

Upvotes: 0

TomJohn
TomJohn

Reputation: 747

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.

Upvotes: 1

Related Questions