Excel - Dynamic ranges without volatile formulas or VBA

Question

I am trying to redo a particular sheet that tracks the status of certain things for each of our clients. I had this built using OFFSET but I want to know if there's a way to do it without volatile formulas or VB script.

The challenging part are the dynamic ranges. They need to be dynamic so that I can enter additional items within a category, or remove them, as need be. The cells in these dynamic ranges will be text. I am checking the cells for keywords indicating severity (ex. good, so-so, bad, very bad), and then displaying the most severe result in a Status header row for that category (which I was doing with nested IF's).

See the image below.

Thus, for Company A, Category Y, the range within which I check for keywords in column D will be D8:D10, and the cell displaying the most severe result is D7, and so forth. If I add Item A4 the range should adjust.

Is this possible without OFFSET or INDIRECT?

As stated earlier, I had the dynamic ranges using OFFSET, then tried with ADDRESS & INDIRECT, but realized INDIRECT is also volatile. Looked at various topics but couldn't find anything specific enough to apply here.

Any help is appreciated.

Answer 1

The INDEX function can replace OFFSET. I'm not sure how you structured your OFFSET or IF functions, but the following will work subject to tweaking:

Company A 2022-01-01
--------------------
=$D$8:INDEX($D$8:$D$11,COUNTA($D$8:$D$11),1)