Nested VLOOKUP, IF, AVERAGE Function

Stab in the dark guys, have a project due but a formula is giving me problems and without the formula I cant build two charts I need.

I am going to explain as best I can what's wrong.

I need to find the average of three sets of figures for three different types of customers. However, the data with the customer keys/types and the figures that need be averaged are in another sheet.

I am trying to use a combination of IF, AVERAGE AND VLOOKUP functions but none seem to be working.

This is the last combination ive tried

=VLOOKUP(a2,avg2,3,false),IF(B5:B11="R"),AVERAGE(C5:C11)


The formula doesnt work because excel instead thinks im referring to B5:B11 in the sheet im typing the formula in and not B5:B11 in the named range avg2 in another worksheet. The same happen with C5:C11 it highlights C5:C11 in the worksheet im typying the formula in and not the avg2 named range in the other worksheet.

B5:B11 represent the customer key for one group of customers and C5:C11 represent the usage level of the same group of customers. I have sorted the table.

Is it possible to use and IF, VLOOKUP, AVERAGE combination and if so can someone please show me where I am going wrong. I need to avoid actually clicking on figures in the other worksheet as I need to avoid relative cell referencing I cant have any avg2! showing in my formulae.

Can anyone assist?

I would be very grateful for any help you can provide.

Thanks in advance.