Using CELL function result as part of range in an AVERAGE formula

[Lanzer]

To define the last cell in the range of row values for an Average formula, with the last cell/column being variable per user selection, I used Index and Match with the CELL and Address function to return the desired single cell reference.

For example: =CELL("address",INDEX(B11:AK11,MATCH(AM$5,B$5:AK$5,0)))
This correctly returns the matching cell reference within the row, such as $Y$11.

But I found that the cell reference result ($Y$11) would not work as the end-part of the range in the Average formula.

For example: =AVERAGE(B11:CELL("address",INDEX(B11:AK11,MATCH(AM$5,B$5:AK$5,0))))
This returns a "There is a problem with this formula" error message.

To check how it works, I tried the resulting cell reference in a simple arithmetic formula, and that wouldn't work either.

For example: =100 + (CELL("address",INDEX(B11:AK11,MATCH(AM$5,B$5:AK$5,0))))
Which I thought would have been calculated simply as =100 + $Y$11, but still the "There is a problem with this formula" error message.

So, apparently the resulting cell reference result is seen as literal text, and not as a normal cell reference that can be used as part of a range in a formula. Any solution ideas?

Thanks!

(Wanted to upload the worksheet as attachment, but there was no option. Also tried posting a "mini-sheet" using L2BB, but that didn't work. So only an image uploaded.)

 

[Fluff]

How about

=AVERAGE(B11:INDEX(B11:AK11,MATCH(AM$5,B$5:AK$5,0)))

 

[Lanzer]

That worked, hmm. That's surprising because I originally went that way, but the result was returning the matched cell value (percentage value), not the cell column-row reference as needed for the Average range, so I didn't try it in the Average formula.

If you have time to explain -
Why does that work, with the Index&Match returning the cell value (the percentage value), which I didn't think could be used in an Average formula range that needs a cell reference (column-row)?
And why does the CELL formula that does return the cell reference (column-row) not work in the Average range, or even in a simple arithmetic formula?

Thanks!

 

[Fluff]

The Cell function returns text not a cell reference, whereas the Index function is returning the cell reference.