ST Alpha - "aha, here's the solution from my favorite channel, and the answer appropriately involves the MAX function. -- (edited)" (Reading a Post)

SlickerTalk
Search Archives
The Leaderboard
The FAQ

Down to Post

Backboards:

Posts: 155

In response to "TTIS spreadsheet help: I want to find the first and last non-blank values a in a row of cells -- (edited)" by Max

aha, here's the solution from my favorite channel, and the answer appropriately involves the MAX function. -- (edited)

Posted by Max Sep 6 '21, 20:59

If the first column in the range is not the A column, here's how to write it (MAX needs an offset, which is provided by "COLUMN()" near the end)

=INDEX($B$1:$H$1,B2:H2,ArrayFormula(MAX(IF(B2:H2="",,COLUMN(B2:H2)-COLUMN()))))

and pull down.

In this case I'm using the header rows as the range of return values even though I'm searching through lower rows.

This is useful for finding the first entry point for a person attending a class, or a character in episodic tv or movie franchise.

So let's say my column headers are the Fast & Furious movies (with Fast 9 in the leftmost column and the first movie in the rightmost column, and my rows are the character names.

For Dom, I can put his row values under most of the movies as "Lead" and can put "Cameo" for Tokyo Drift. Using the formula above, it will return "The Fast and the Furious" (his first movie rather than "Lead")

https://www.youtube.com/watch?v=gY7fVMGP7Dk (www.youtube.com)

Post a message top

aha, here's the solution from my favorite channel, and the answer appropriately involves the MAX function. -- (edited)

Replies are disabled on threads older than 7 days.