CODEWITHSUNDEEP
google-sheetsindexingmatch
jivers
jivers

Reputation: 1000

Index and Match a Subset Index in Google Sheets

I find the MAX of a metric called Positive Gamma (column J), and a corresponding value called Strike (column A) using MATCH. This match is the Key Gamma Strike.

=INDEX($A$2:$A, MATCH(MAX($J$2:$J), $J$2:$J, 0), 0)

Now I need to find the max Positive Gamma among all strikes above the Key Gamma Strike.

For example, if the Key Gamma Strike is 380, and the strike range goes to 400, I need to find the strike between 381 and 400 with the highest Positive Gamma.

I find the row location for each strike above the Key Gamma Strike, placing it into column K, as follows:

=IFERROR( INDEX( IF( MATCH($A$2:$A$550, $A$2:$A$550, 1) > MATCH(MAX($J$2:$J$550), $J$2:$J, 0), Match($A$2:$A$550, $A$2:$A$550, 1), "") ,0, 0), )

I'm struggling with finding the highest positive gamma from this range, and the corresponding strike.

There may be a better way to get this, I'm just not sure how. Feedback appreciated.

Here's a file with data matching the above formulas: https://docs.google.com/spreadsheets/d/1VGGjd1f47yCz9mvDHqRFEFYWJTq0w-Y8Mf8iGqFgO28/edit?usp=sharing

Upvotes: 0

Views: 153

Answers (1)

Mario Javier Eugenio Estrada
Mario Javier Eugenio Estrada

Reputation: 337

Assuming that you have the Key Gamma Strike value on the cell K1, you could use the following array formula:

=ARRAYFORMULA(MAX(IF($A:$A>$K$1;$J:$J;"")))

You can find an example here.

Upvotes: 2

Related Questions