Search code examples
excelexcel-formulaformulalookupexcel-2019

Find adjacent cell values based on 'looked up' cells' original position whilst dealing with duplicate values

I'm currently stuck with an excel problem that I can't seem to find an answer to.

Take the following example dataset:

A          B          C          D          E          F          G     
Date       Color                                       Lookup     Adjacent value
1-1-2017   Green                                       3-5-2018
3-5-2018   Yellow                                      4-7-2018 
4-7-2018   Green                                       9-9-2018
1-2-2016   Purple                                      3-5-2018
6-9-2014   Red
9-9-2018   Green
3-5-2018   Blue

Values in column A and B are hardcoded.

Values in column F have been retrieved with the following formula that 'filters' the main data based on dates between 1-1-2018 and 31-12-2020 (in my real dataset column A and B will be on a different sheet than the lookup values and the required adjacent values):

=IFERROR(INDEX($A$1:$A$8;AGGREGATE(15;6;ROW($A$1:$A$8)/(($A$1:$A$8>=DATE(2018;1;1))*($A$1:$A$8<=DATE(2021;1;1)));ROW($A1)));"")

I now need to find a way to retrieve the adjacent cell values from column B (e.g. color) based on the lookup values in column F and place them in column G. The dataset should then look as follows:

A          B          C          D          E          F          G     
Date       Color                                       Lookup     Adjacent value
1-1-2017   Green                                       3-5-2018   Yellow
3-5-2018   Yellow                                      4-7-2018   Green
4-7-2018   Green                                       9-9-2018   Green
1-2-2016   Purple                                      3-5-2018   Blue
6-9-2014   Red
9-9-2018   Green
3-5-2018   Blue

It is important for the sequence to remain intact; even though column A contains a duplicate of 3-5-2018 they are not the same due to their color being different.

My main question: How do I attach the correct color to the correct lookup date value?

Any help would be so very much appreciated!

Kind Regards,

VHes

Solution

  • First you need to change array of index function from $A$1:$A$8 to $B$2:$B$8. Try below formula.

    =IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8=F2),COUNTIF($F$2:$F2,F2))),"")

    enter image description here