I want to find the row number of the nearest match, e.g. in the example below, Previous is the row# of the nearest matching value above.
It’s easy to find the nearest matching value below. But for above I’m stumped.
Row Fruit Previous
--- ----- --------
2 Banana #N/A
3 Apple #N/A
4 Orange #N/A
5 Grape #N/A
6 Banana 2
7 Apple 3
8 Grape 5
9 Banana 6
10 Apple 7
nm - I figured it out. The actual layout is below:
Row Fruit Prev Next
2 Banana #N/A 6
3 Apple #N/A 7
4 Orange #N/A #N/A
5 Grape #N/A 8
6 Banana 2 9
7 Apple 3 10
8 Grape 5 #N/A
9 Banana 6 #N/A
10 Apple 7 #N/A
You can take advantage of the “Next” column. In row 7 for example:
Previous cell contains “=MATCH(ROW(B7),$D$1: D7,0)”
Next cell contains “=MATCH(B7,B8:$B$11,0)+ROW(B7)”