Try it like this:

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

--
Biff
Microsoft Excel MVP


"L. Howard Kittle" wrote in message
. ..
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