Replace words by numbers and calculate mean

Hello,

I have a list of words in one column with a number assigned to each of them in another column, in another column I have several of those words of the list (and others that are not in the list) and I'm trying to calculate the mean of each cell, only using the words that are in the list, as in the example that I attach, "blue" and "green" are not in the list so I don't have to consider them for the calculation of the mean of A2.
I tried to find and replace simoultanously but doesn't work, I have more than 100000 rows in the "text" column and several thousands of words in the "word" column..

=sum($e$2:$e$31*isnumber(find($d$2:$d$31,a2)))/sum(--isnumber(find($d$2:$d$31,a2))) cse

Thanks a lot tim201110! but I could not make it work, I get the division by zero error I think it is because the

Please Login or Register  to view this content.

doesn't work, it must find all values in

Please Login or Register  to view this content.

in a2 but when I test it alone

Please Login or Register  to view this content.

I get error. I tried to solve it with this
https://exceljet.net/formula/range-c...-specific-text
but does not work

with a space on the right
it is more accurate

Thanks a lot!!!!!

I thought that the problem was solved, but now I'm trying the formula with more data and it is not working fine.
Here I attach the old excel file (points.xls) and the new one (points3.xls)
In points3.xls I tested the words in A2: "Getting ready for lights out" and the result for this should be 5,684 as shown in K7, but the formula calculates 5,13 as shown in G2.
I attach both files.. I think that the problem is that now in points3.xls it finds more words in D column that match those in A2 for example it finds "ready" but it also finds "already" and uses both of them to calculate the average.. but I can't find out how to solve that

Thanks a lot works great!