Vlookup same item but after certain date

[ExcelNoob222]

Hi all, I have two sets of data and I need to vlookup from one to the other. The problem is dates.

First table:

Item	Date	Price
1​	1-Jan-21​	6​
1​	15-Jan-21​	10​
1​	25-Jan-21​	14​

My second set of data:

Item	Date	Adjusted Price
1​	1-Jan-21​	7​
1​	2-Jan-21​	9​
1​	5-Jan-21​	9.5​
1​	19-Jan-21​	11.75​
1​	24-Jan-21​	12​
1​	26-Jan-21​	14.5​
1​	28-Jan-21​	16​

I need to vlookup the adjustd price column from the second set of data into the first set. However, I only want the row for each item that is on or after the same date from the first table. As an example my combined data would like this:

Item	Date	Price	Vlookup
1​	1-Jan-21​	6​	7​
1​	15-Jan-21​	10​	11.75​
1​	25-Jan-21​	14​	14.5​

 

[jorismoerings]

Hi,

Assuming first table range = A1:C4
second table range = A10:C17
try this in D2

=VLOOKUP(MIN(FILTER(B10:B17, B10:B17>=B2)),B10:C17,2,0)

 

[DanteAmor]

Try this:

Dante Amor
	A	B	C	D	E	F	G	H	I
1	Item	Date	Price	Vlookup			Item	Date	Adjusted Price
2	1	01/01/2021	6	7			1	01/01/2021	7
3	1	15/01/2021	10	11.75			1	02/01/2021	9
4	1	25/01/2021	14	14.5			1	05/01/2021	9.5
5							1	19/01/2021	11.75
6							1	24/01/2021	12
7							1	26/01/2021	14.5
8							1	28/01/2021	16
Hoja1

Cell Formulas
Range	Formula
D2:D4	D2	=INDEX($I$2:$I$8,MIN(IF($H$2:$H$8>=B2,ROW($I$2:$I$8)))-ROW($I$1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ExcelNoob222]

Hi,

Assuming first table range = A1:C4
second table range = A10:C17
try this in D2

=VLOOKUP(MIN(FILTER(B10:B17, B10:B17>=B2)),B10:C17,2,0)

Hi! Thank you for your answer. This did work, however, I should have mentioned my actual data has more than one item. So the same problem happens again but with item 2, then 3 and so on. Is there a way to adjust this formula to also factor in the item number? Some items will have the same date on the first list but a different date on the second list so I think I will get the wrong info if I do not factor in the item.

Thank you!!

 

[ExcelNoob222]

Try this:

Dante Amor
	A	B	C	D	E	F	G	H	I
1	Item	Date	Price	Vlookup			Item	Date	Adjusted Price
2	1	01/01/2021	6	7			1	01/01/2021	7
3	1	15/01/2021	10	11.75			1	02/01/2021	9
4	1	25/01/2021	14	14.5			1	05/01/2021	9.5
5							1	19/01/2021	11.75
6							1	24/01/2021	12
7							1	26/01/2021	14.5
8							1	28/01/2021	16
Hoja1

Cell Formulas
Range	Formula
D2:D4	D2	=INDEX($I$2:$I$8,MIN(IF($H$2:$H$8>=B2,ROW($I$2:$I$8)))-ROW($I$1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi, Great answer! This did work but just like the other suggestion, it does not use the item number. So the same problem happens again but with item 2, then 3 and so on. Is there a way to adjust this formula to also factor in the item number? Some items will have the same date on the first list but a different date on the second list so I think I will get the wrong info if I do not factor in the item.

 

[jorismoerings]

To factor in the item number we need to change the filter part, like this

=VLOOKUP(MIN(FILTER(B10:B17, (B10:B17>=B2)*(A10:A17=A1))),B10:C17,2,0)

 

[DanteAmor]

it does not use the item number

Try this:

Dante Amor
	A	B	C	D	E	F	G	H	I
1	Item	Date	Price	Vlookup			Item	Date	Adjusted Price
2	1	01/01/2021	6	7			1	01/01/2021	7
3	1	15/01/2021	10	11.75			1	02/01/2021	9
4	1	25/01/2021	14	14.5			1	05/01/2021	9.5
5	2	01/01/2021	6	9.5			1	19/01/2021	11.75
6							1	24/01/2021	12
7							1	26/01/2021	14.5
8							1	28/01/2021	16
9							2	01/01/2021	9.5
10							2	02/01/2021	11.5
11							2	05/01/2021	12
12							2	19/01/2021	14.25
Hoja4

Cell Formulas
Range	Formula
D2:D5	D2	=INDEX($I$2:$I$12,MIN(IF(($H$2:$H$12>=B2)*($G$2:$G$12=A2),ROW($I$2:$I$12)))-ROW($I$1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ExcelNoob222]

To factor in the item number we need to change the filter part, like this

=VLOOKUP(MIN(FILTER(B10:B17, (B10:B17>=B2)*(A10:A17=A1))),B10:C17,2,0)

Hi! So this gave me the correct results for the first item but once the formula reached the second test item, it brought back the price for item 1 and not item 2.

 

[ExcelNoob222]

Try this:

Dante Amor
	A	B	C	D	E	F	G	H	I
1	Item	Date	Price	Vlookup			Item	Date	Adjusted Price
2	1	01/01/2021	6	7			1	01/01/2021	7
3	1	15/01/2021	10	11.75			1	02/01/2021	9
4	1	25/01/2021	14	14.5			1	05/01/2021	9.5
5	2	01/01/2021	6	9.5			1	19/01/2021	11.75
6							1	24/01/2021	12
7							1	26/01/2021	14.5
8							1	28/01/2021	16
9							2	01/01/2021	9.5
10							2	02/01/2021	11.5
11							2	05/01/2021	12
12							2	19/01/2021	14.25
Hoja4

Cell Formulas
Range	Formula
D2:D5	D2	=INDEX($I$2:$I$12,MIN(IF(($H$2:$H$12>=B2)*($G$2:$G$12=A2),ROW($I$2:$I$12)))-ROW($I$1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Awesome, this worked! Thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.