Combination of AVERAGE with VLOOKUP and MATCH, INDEX?

Hi Everyone,

This is my first post on the forum, it feels good to finally have created an account have found several solutions to problems in the past. However, after googling, and reading up here on the forum I cannot seem to find an easy fix to my problem.

What I want to do is the following, I have two sheets, one where the data needs to be filled and the second where the date needs to be looked up. In Sheet1 I need to find a date for each of the NR2 and NR1 combination. But in the second sheet there are multiple NR1 occurences and also single occurences. So if there is only one, I need that date, if there are several I need the average of all the occurences for NR1, not taking into account the N/A ones.

(some examples from the file)
NR2 NR DATE
100707987 121951
100702347 121960
100707750 121960
100707721 121960
100702422 121960
100702203 121961
100712126 121962
100712317 121962
100712316 121962
100702427 121965
100702360 121965
100708939 121967

NR1 NR2 DATE
121951 100707987 #N/A
121951 100705469 30/07/1984
121960 100703618 25/09/2007
121960 100702347 #N/A
121960 100707750 #N/A
121960 100707721 #N/A
121960 100709354 28/09/2007
121960 100702422 #N/A
121961 100696998 9/02/2007
121961 100702203 #N/A
121962 100711784 24/08/2006
121962 100712126 #N/A
121962 100712317 #N/A
121962 100712316 #N/A
121965 100703520 3/09/2002
121965 100698888 27/01/2004

So for example, NR1 121965 has two dates, 03/09/2002 and 27/01/2004, so here it should calculate the average of these two and put that average in the first sheet.

I was thinking of something like IF(MATCH(?) gives one result,put that with vlookup, else AVERAGE of all MATCH that are not N/A)

Hope somebody can help me out!


Thanks in advance!

Sktneer, I think this is exactly what I need, wauw!! Sometimes the solution can be so easy u don't want to see the constructions I was trying to make