Use array for lookup value, to return array of lookups

[Glen]

I have a row of values that I want to average, but the cells that need to be
averaged contain text that has to be matched to a numerical value for the
purpose of averaging.

eg "Fair","Poor","Excellent","Poor" etc etc as values in the row

where
Excellent = 100
Good = 85
Fair = 75
Poor = 50

So for a row where the text data is in C2:T2 and the lookup table with the
corresponding numerical values is called range "Numerical_LU", I'm trying to
use the following formula in C1:
{=average(vlookup(C2:T2,Numerical_LU,2,0))}

But all I can get is the value of the lookup for the first cell (C2). Is
there are way to get vlookup to evaluate the whole array of C2:T2 and return
the results as an array?

Cheers,

Glen

 

[T. Valko]

Try this:

=SUMPRODUCT(SUMIF(INDEX(Numerical_LU,,1),C2:T2,INDEX(Numerical_LU,,2)))/COUNTA(C2:T2)

 

[Glen]

Works a treat.

Cheers, Glen

Try this:

=SUMPRODUCT(SUMIF(INDEX(Numerical_LU,,1),C2:T2,INDEX(Numerical_LU,,2)))/COUNTA(C2:T2)

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[how_llewoh]

lookup inquiry

i would like to know whether it is possible to average lookup values without putting into a new sheet? if you have a column of data, can it be possible to lookup and at the same time take the average of the values being looked up? what could be the formula for the solution on this problem of mine? thank you in advance. GODSPEED!