Vlookup to show average of a range

Hello All,

I need help with Vlookup or any other function which can show me the average for a range,

I do not want to use an extra column to calculate the Average.

I am enclosing a sample file with the result I expect and the formula I tried which is not working

Basically, I was trying to use the AVERAGE function for Column Index number.

Vlookup for Average.xlsx

Any help would be greatly appreciated.

TIA

Hello Carim,

Thanks for the formula.

As mentioned in the OP, I do not want to use the extra column F of Sheet 1 . It was typed for explanation purpose only.

I tried the formula which failed shown on Sheet 2 Column F.

Is there a possibility to use the AVERAGE within the function.

Dear Carim,

Thanks for the formula and your time.

Works great.