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

Question

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

Answer 1

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))),"")

Answer 2

This is the exact reason why the function VLookup has been invented, let's have a look at following formula (to be entered in cell G2):

=VLookup(F2;$A$1:$B$8;2;FALSE)

This function looks for:

• F2 : we start by looking for date "3-05-2018"
• $A$1:$B$8 : we are looking for that value in the range A1:B8, why:
  • the value we are looking for, is present in the first column of that range (column A)
  • the value we want to return, is also present in that range (column B)
  • we must keep that range invariable: when we drag that formula to the next row, it may not alter into "A2:B9", therefore we must use "$A$1:$B$8"
• 2 : we are looking for the value of the second column
• FALSE : we are looking for the exact match, not an approximative one.

In case you have questions, feel free to ask.