An INDEX/MATCH function to retrieve vlaues from a table and AVERAGE them (arrays)

Hi there. First of all I want to say thanks for all the help people on this forum have given me so far. I have learned so much and you are all wonderful.

Now the problem I have.

I have 2 sheets recording feedback and summarising the overall percentages for each trainer.

My boss wants me to do this by month!?!?!

I have created a third sheet but I am not sure how to do the following:

• Look up the trainer name
• Look up the month
• identify the percentages for each category
• create an overall average of these percentages

So for example if Jon smith trained twice in JAN getting 100% and 50% in cat1, it would display 75% in the cat 1 cell and so on.

I have attached the file.

Once again thank you so much.

Simon

Try:

=AVERAGE(IF(COUNTIF(trainer,$A4),IF(TEXT(date,"mmmm")='Monthly Summary'!$A$3,IF(trainer='Monthly Summary'!$A4,cat1),0)))

in B4, which must be confirmed with CTRL+SHIFT+ENTER, not just ENTER

and then copied down all the January names...

You can copy formula across, but will have to adjust the cat1 to cat2, cat3, etc...

If you copy formulas from B4:G4 to B32:G32, you will have to change the $A$3 to $A$31 to reference February...

Repeat for all months.

To hide 0 values, you can go to Tools|Options and from the View tab uncheck Zero Values.

This is so confusing!

You bet.

You are going to have to try again, with more examples and how you get those numbers.

Try this in B4:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down for January...

you will need do the the appropriate adjustments as outlined before for the different cats and different months...

not sure exactly what you mean, but if you mean that the delegates are all zeros

Please Login or Register  to view this content.

or if the cat1 are all 0's

Please Login or Register  to view this content.

or post what you mean so that I can see and possibly avoid that error....