Use of MATCH and INDEX to find first and last instances meeting criteria

Hi,

I am trying to write a formula which finds the last instance of a number greater than 30 in a column (B). The values are not sorted from smallest to largest as they correspond to a time series (A) which needs to be preserved. Ultimately, I want the formula to return the time at which this value occurs, but I think I can do that bit using INDEX

I have a formula to give the first instance (row number) of a number above 30, which seems to work, but beacuse I don't fully understand how it works I can't modify it to give the last instance. The formula is:

=MATCH(1,INDEX(--(B2:B883>=30),0),0)

Having only just learnt how to use MATCH and INDEX, I thought each should have 3 arguments, so I'm confused about how the 4 arguments here work. Also I can't find much on using logic functions within INDEX. Can someone explain this formula, and suggest how I might go about finding the last instance please?

I've attached the data if it helps.

Thanks

To get the End Time:

Please Login or Register  to view this content.

I just re-read and noted:

Having only just learnt how to use MATCH and INDEX, I thought each should have 3 arguments, so I'm confused about how the 4 arguments here work. Also I can't find much on using logic functions within INDEX. Can someone explain this formula, and suggest how I might go about finding the last instance please?

Your INDEX is embedded within a MATCH - ie forms one of the MATCH parameters... so your MATCH does in fact have 3 arguments, ie:

MATCH(1,INDEX(),0)
note 3rd parameter is optional but is by default 1 rather than 0 which is why it's specified here (exact match test)

INDEX is a little different - it can be used a number of ways ... here it's being used to populate your array of values to be used in the MATCH

INDEX(--(B2:B883>=30),0)

Is essentially creating an array of 0/1 values ... one for each cell referenced where 0 means value in associated cell is < 30 and 1 means >= 30
The 1/0 is achieved via double unary (--) which coerces the Boolean TRUE/FALSE output of the test to 1/0 respectively.

In this instance you could in fact use:

MATCH(TRUE,INDEX(B2:B883>=30,0),0)

ie avoid the coercion altogether

Why is the INDEX using only 2 parameters and not 3 ?

Well here the INDEX range is a Vector - ie a single column so we need not specify which column we intend to return given only one exists we need only indicate the row

Why is row 0 ?
When 0 is used ALL rows are returned rather than just a specific row in the range... thereby returning an array of 882 TRUE/FALSE values (or 1/0 if coerced) against which the MATCH is applied and it finds the first TRUE (or 1) in the range and returns the row index accordingly.

Does that help ?

(Re: vector - the same holds true for a single row - ie you need only specify the column given one row and 0 would return all columns)

I normally refer to the below in which I tried to explain the concept some time ago - others have done so better I'm sure

http://www.excelforum.com/2100732-post14.html

Although in the above the conditional test is not the same the basic premise of the approach is identical.