Is AVERAGEIFS the correct function to use?

Hi,

I'm sure there is a straight forward way of doing this but I have confused myself with trying to combine vlookup and averageifs etc.

I need to return a value to A2 by matching the text in A1 to a cell/row in my large table of values elsewhere in the workbook.

Once the row has been identified I'd like to average the values based on several criteria.

The problem I seem to be having is the AVERAGEIFS function requires an average range (1 row of cells). When I'm working with a large array of cells.

Any thoughts would be appreciated.

Please post a workbook.

Please see attached file.

I am hoping to find the combined average score for the pupils during lessons 2,3,4 & 5 using a formula that can be copied onto a much larger worksheet.

Thanks for your reply Haseeb.

I have confused things with my wording (ignore combined). I was trying to just return averages in C3, C4, C5 & C6.

Ideally I would like to find the average using >1 and <6 within the formula for easy amending this going forward.

Would this simplify the formula suggested?

I have attached the amended spreadsheet to avoid confusion.

I am trying to match text and then apply the above criteria on the matched row of cells.

Problem solved! Thanks for both formulas. Much appreciated.