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!