Combining Average, Offset & Vlookup

[L. Howard Kittle]

Excel 2002

Hi Excel users and Experts,

I'm trying to lookup a value in a list, and average the four values 1 column
to the right and -4 rows high. This formula does that.

=AVERAGE(OFFSET(F9,0,1,-4,1))

The F9 is a cell in the list column and I want to replace F9 with a vlookup
or a lookup or a match where the lookup or match reference is supplied by a
dropdown in B2.

These do not work and error out. Tried index & match but also failed
miserably . A Google search danced all around what I need.

AVERAGE(OFFSET(lookup(B2,F2:G16,1,0),0,1,-4,1))
AVERAGE(OFFSET(vlookup(B2,F2:G16,1,0),0,1,-4,1))
AVERAGE(OFFSET(indirect(lookup(B2,F2:G16,1,0),0,1,-4,1)))

Thanks,
Regards,
Howard

 

[T. Valko]

Try it like this:

=AVERAGE(OFFSET(INDEX(F2:F16,MATCH(B2,F2:F16,0)),,1,-4))

 

[L. Howard Kittle]

Hi Biff,

Right on the money! Thanks you very much for the help. I can see I would
have never really been able to put it together.

Thanks again,
Regards,
Howard

 

[L. Howard Kittle]

Hi again Biff,

I added an error checker to your suggested formula, as there may be a
selected value from the dropdown that won't have three values to average to
the selected value.

=IF(ISERROR(AVERAGE(OFFSET(INDEX(F2:F16,MATCH(B2,F2:F16,0)),,1,-4))),"There
are not 4 months to
average",AVERAGE(OFFSET(INDEX(F2:F16,MATCH(B2,F2:F16,0)),,1,-4)))

I ground a few theeth getting all the )))'s & the ((('s in the correct place
but knowing I had a solid formula I got'er done.

Thanks again,
Howard

 

[T. Valko]

You're welcome. Thanks for the feedback!

Another way to write the same formula:

=AVERAGE(OFFSET(F2,MATCH(B2,F2:F16,0)-1,1,-4))

The difference between the 2 formulas is like 6 of one, half dozen of the
other!