How can I use the Index function to find the last specific values in a column?
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.
Answers

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
Help Article Resources
Categories
Check out the Formula Handbook template!