So for C3 in your sample, you want a formula that does this:
1) Checks the nomenclature key in B3 (that's 243, in this case)
2) Finds all the rows in Sheet2 that have that nomenclature key in col A
3) Displays the average of the corresponding values in col B
Right? Seems to me you want COUNTIF and SUMIF. No, wait, I just looked it up; Excel now has an AVERAGEIF function, so it's even simpler. I've never used it, but according to the documentation it should go like this:
Code:
=AVERAGEIF(Sheet2!A:A,B3,Sheet2!B:B)
Which says "Look in Sheet2 col A for any rows that equal B3, and for those rows return the average of the values in Sheet2 col B".