What's wrong with my Average IF formula?

hi everyone,


I wonder if you can help me with a question I have;


I want to use the word in Column A to search another sheet - every time it finds a particular word in a sentence (column B) it uses the OR figure in the cells next to the sentence (column C) and calculates an average for those figures.


I ended up with "=AVERAGEIF($A$3:$A$20812,A3,Subject_Input!$C$400:$C$427)" 


This works for the first 20 or so cells but after that, it stops working even though I know the figures are there.


Does anybody have any suggestions on what my problem could be after I get past the 20th cell?

EDIT:

Here's a copy of the sheet for reference: https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fdrive.google.com%2Ffile%2Fd%2F1EXSv1YuB7T9oIgmV2BUESW7yS1rN4Vbi%2Fview%3Fusp%3Dsharing


I'll reference the sheet as I explain it a little better;

• On the sheet titled 'weighting', you'll see over 20,000 individual words, emojis, etc in column A.
• In column D of the same sheet, you'll see percentage figures titled 'OR'.
• On the sheet titled 'subject_input', you'll notice next to each 'full subject' (column B) there are OR% figures.

What I want to do is find the average OR% figure for each word if that makes sense?


For example;
If we're using the word 'travel in cell A10 of 'weighting'.


Every time the word travel appears in the full subject (column B) 'subject_input' then the OR figure next to it would be totalled up and averaged to produce an overall average for those particular words.


To try and give a simpler example I've created this sheet; https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fdrive.google.com%2Ffile%2Fd%2F1gQEsFqkDHfIvwdNxOVmfu2U6bU_m8WFM%2Fview%3Fusp%3Dsharing


am I making much sense here?

If anyone can help me out here I'd really appreciate it!