Index function with duplicates.

[happy_smiler1]

Hi,

Im trying to figure out a way of being able to lookup the date in column D when there are duplicate fields, in Column B but there is a different code in column A.
In the attached spreadsheet, I want to know in column G the first date, and then the second date in column H for the same add code (the duplicate), I have tried 2 way lookups, but to no avail.

Here is what I have tried

=index($D4:$D9,MAX,(if($G4=$A4:$A9,if($F4=$B4:$B9,row($D4:$D9)-row($D4:$D9)+1))))


I do apologise if it is confusing but I have tried different formulas and none seem to work. Any help would be greatly appreciated.

Thank you in advance.

[JBeaucaire]

In H4 you could put this array formula:

=INDEX($D$4:$D$9, SMALL(IF($A$4:$A$9=$G4, IF($B$4:$B$9=$F4, ROW($D$4:$D$9)-3, ""),""), COLUMN(A1)))

...confirmed by pressing CTRL-SHIFT-ENTER. You will see curly braces { } appear around your formula and the first answer will appear. Now copy that cell down and across to the next column.


Change the value in A7 to VT0001 and you will it now answers into cell I4.

[happy_smiler1]

Woooohoooooo thank you very much, will try it out.

[happy_smiler1]

Thank you for that, if there were more dates but different year, how would the above formula work to take the earliest date i.e 2008 as the first date then 2010 as the second date and 2012 as the third date?
Thanks You

[JBeaucaire]

Sort the list so dates ascend... earliest dates at the top of the list.

[happy_smiler1]

The date order is not an issue, apologise for that, In the formula above: IF($B$4:$B$9=$F4, there could be many codes that relate to one add code i.e a code VT0001 and VT0004 could relate to add code 123, if i changed the formula to IF($B$4:$B$9= $F$4:$F$9, would this be accepted or would it return an error as im not specifying a specific cell but specifying a column?