average and vlookup

Hi All

Apologies first off - but I can't upload a sample worksheet due to my company's security settings, so I'll try to explain what I need...

I have a list of categories in A5:A20 - these are A,B,C,D etc... etc... In B5:B20 are numeric values 1,3,5,7 etc... etc... This is my lookup table.

Additionally, I have a chart with monthly headers F4:Q4. Under F4 I have 20 values A,C,F,B,A,D etc... (F5:F24).

What I need

I'd like a formula that looks up all of the values F5:F24 in my lookup table and returns the average of the total sum.

I realise I could use a helper column to do the lookups but I have multiple months to apply this to and don't want to double the amount of columns.

Any help gratefully received.

SAA

benishiryo - thank you for taking the time to respond. The array formula you have posted returns a #N/A error.

benishiryo - your spreadsheet is set out exactly as mine. I've noticed that if any of the values in column F are deleted then the #N/A error occurs. This is why it's failing for me. In some instances there may be blanks.

SAA

Right then. I've figured it out - the following formula works for me:

=SUMPRODUCT(SUMIF($A$5:$A$20,E5:E24,$B$5:$B$20))/COUNTIF(E5:E24,"<A")

SAA