Averaging rents based on two criteria

Hi all,

I have a specific use case I am trying to build a formula for. I am thinking it might include some combination of XLOOKUP and INDEX MATCH MATCH, both of which I've used before, but I this is beyond my pay grade and I am struggling to figure out a solution (it might include neither of those functions).

Attached is a sample file.

The formulas are to go in cells Q2:Q6 (highlighted green). My objective is as follows:

Q3 would be the average of B3:L8 IF P3 matches the corresponding number in B2:L2 AND IF S2 matches the corresponding letter in column M. So if the "Subject" (S2) has a B "impr. rating", I am trying to have it calculate the average rent of 50 sq ft unit sizes (column B represents market rents for 50 sq ft units, and Q3 is adjacent to 50 sq ft in column P.

You'll notice some of the columns don't have data, so I imagine an IFERROR would need to be incorporated into here somehow.

As I'm typing it out I realize it probably sounds confusing. But hopefully viewing the file provides clarity.

Any help would be appreciated!

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "Need Help with Specific Formula, Might Include XLOOKUP and/or INDEX MATCH MATCH" assumes the solution and does not describe your question. I have updated it for you this time because you are a new member. --6StringJazzer

Use this formula in Q2 and copy down:

Formula:

Please Login or Register  to view this content.

Try this
In Q2

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

Use this formula in Q2 and copy down:

Formula:

Please Login or Register  to view this content.

Sorry, this is the sum, not an average. Looks like kvsrinivasamurthy has the complete solution.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.