Excel Offset: Automating the Reference Cell by using Index Match

Question

If the answer was already provided, please feel free to post the link, but I did not see one.

This is a real estate example. I have a table of a lot of properties and information. One of the fields is "Occupancy." I am trying to use the AVERAGE OFFSET functions combined with CELL and INDEX/MATCH to calculate the average Occupancy of the top 5 and top 10 buildings in this table. To accomplish this, I wanted to create a "Summary Table" below the Property table to list the averages.

Specifically:

1. For OFFSET, in the "Reference" section, I want to look up "Occupancy" in my summary cell, use INDEX MATCH to find "Occupancy" in the table, and then;

2. I want the Cell Reference returned AS A VALUE and then move one row down and then highlight the top 5.

3. Most importantly, I want everything cell referenced without hardcoding or navigating the table to highlight the cell reference. I am not sure if this is possible, but after pouring through a bunch of articles, I am having trouble finding a solution.

To make things simple, let's pull out all the columns I had in the original table and go off the below. Assume all the data types are in the proper format:

     **COLUMN A**       **COLUMN B**
ROW 1 | Properties   | Occupancy   |
ROW 2 | Property A   | 58%         |
ROW 3 | Property B   | 56%         |
ROW 4 | Property C   | 77%         |
ROW 5 | Property D   | 85%         | 
ROW 6 | Property E   | 92%         |

ROW 7

ROW 8 |Top 5         |              |  'NOTE: I used a custom formula to format:                  
                                     "Top "#. Please do not think this is text.

ROW 10|Occupancy     | **CODE HERE**| 'Format is "General"
ROW 11|              |              |
ROW 12|              |              |

Here's how I initially coded it in B10:

'=AVERAGE(OFFSET(CELL("address",INDEX($A$1:$B$1,MATCH(A10,$A$1:$B$1,0))),1,0,5,1))

I am getting "there is a problem with your formula" since I am guessing it is due to the cell returning text in lieu of the cell reference as a value. So I error checked myself and stripped out the AVERAGE, OFFSET and put this is B11:

'=CELL("address",INDEX($A$1:$B$1,MATCH(A10,$A$1:$B$1,0)))

I know I get $B$1, so I then put $B$1 to replace the CELL function within B10 to see if my average offset is wrong:

=AVERAGE(OFFSET($B$1,1,0,5,1))

I now get the right solution since I went and hardcoded the reference, which does not solve the issue.

If I use INDIRECT and then CELL in B12:

'=INDIRECT(CELL("address",INDEX($A$1:$B$1,MATCH(A10,$A$1:$B$1,0))))

I then get the title "Occupancy" and not the cell reference.

Obviously, I am aware of the fact that the CELL function will return text and not a value. I can't use INDIRECT because when I do, INDIRECT will just display the title of the column in text and not return the cell reference as a value. ADDRESS is not useful because you have to hardcode and the goal is to have zero hardcodes.

How can I code finding the cell reference of specific text in a table without actually going to the table to cell reference when I am building out my OFFSET function?

As you can see, I went through a variety of ways to dissect the problem and I would appreciate anyone's help to come to a solution. Thanks in advance.