Excel Reverse Match

Question

I'm trying to find a way to do a reverse match in Excel. I have a column of TRUE/FALSE statements and I need to find a way to find both a match and a reverse match within this one column to find the difference between TRUE statements in both directions.

True
False
False
False
True
False
False
True

In this scenario from the perspective of the middle True statement the normal MATCH function going down would return a number of 4. I am trying to find an additional match function that would look upward to the TRUE statement and return a value of 5

Answer 1

Quick and dirty:

A1:A7: TRUE/FALSE
A8: Array formula: {=LARGE(IF(A1:A7=TRUE;ROW(A1:A7);"");1)}

The result will be the row number of the nearest TRUE from below. The formula is inserted by Shift-Ctrl-Enter in a formula window. Curled brackets are inserted by Excel, not by a user.

If you need the shift from the last TRUE: {=LARGE(IF(A1:A7=TRUE;ROW(A1:A7);"");2)}

Changing the last number in the formula moves you to the n-th TRUE from below.

Replacing LARGE with SMALL will give the row numbers from above. So it would possible to find the shift between relevant combinations by combining the formulae.