0

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

JvdV
  • 70,606
  • 8
  • 39
  • 70
VHes
  • 15
  • 5

2 Answers2

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

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Thanks man, you're a hero. It works perfectly. I'm going to try and figure out how it works exactly now :) – VHes Dec 03 '20 at 10:47
  • I am new to this website. Didn't know that was an option but I found it! – VHes Dec 03 '20 at 12:06
0

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.

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • 1
    `VLookup()` will fail for duplicate data. It will not show last result `3-5-2018 Blue` – Harun24hr Dec 03 '20 at 10:52
  • Yes, I tried this Dominique but as Harun mentioned this will go haywire for the duplicates. Thank you for your input though, I appreciate it. – VHes Dec 03 '20 at 11:08
  • Ok, I hadn't understood that part, but while searching on the internet, I've found that reversing VLookup (or using it backward) seems to be possible in combination with the `Choose` function, maybe you can use this approach? – Dominique Dec 03 '20 at 13:25
  • @VHes: I've created a new question for this (https://stackoverflow.com/questions/65127138/is-it-possible-to-fix-the-direction-of-excels-range-object), and apparently, in Excel 365, there's a new function `XLookup`, possible to reverse `VLookup` search. – Dominique Dec 03 '20 at 13:57