How can I use the Index function to find the last specific values in a column?

Jeff Hoelzel

Here is the formula I started with: =IF([Hide Level 1]37 = "N", "", IF(INDEX(Format$1:Format36, COUNT(Format$1:Format36) + 0) = "W", "B", IF(INDEX(Format$1:Format36, COUNT(Format$1:Format36) + 0) = "B", "W")))

I am trying to index the Format column to find the last "B" or "W" so the formula will result in a "W" if the previous result was a "B" or a "B" if the previous result was a "W" while ignoring the blanks when Hide Level 1 is "N"

The blanks are throwing the formula off. Row 43 should have "B" in the Format column then a "W" in the Format column on row 44.

The idea is to get the formula to result in alternating "B's" and "W's" in the format column, use conditional formatting, filter, and achieve a banded row look.

Any help on this would be greatly appreciated.

Jeff Hoelzel

Figured it out. Here's the formula in case anyone else might need something similar: =IF([Hide Level 1]37 = "N", "", IF(INDEX(Format$1:Format36, MATCH(MAX(COLLECT([Row ID]$1:[Row ID]36, Format$1:Format36, ISTEXT(@cell))), [Row ID]$1:[Row ID]36, 0)) = "B", "W", IF(INDEX(Format$1:Format36, MATCH(MAX(COLLECT([Row ID]$1:[Row ID]36, Format$1:Format36, ISTEXT(@cell))), [Row ID]$1:[Row ID]36, 0)) = "W", "B")))

This also works when refreshing linked data old and new then sharing a filter.

*change range and specific values for fit