Looking Up Data Based on a Future Date and Another Criteria

[KellyBailey]

Hello,

Hope someone can help with this. I have a worksheet with a list of products, dates, delivery locations and amount being delivered (units). The products can be duplicated, as can the dates, but the delivery locations will be different if this is the case.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Date[/TD]
[TD]Delivery Location[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]Cup[/TD]
[TD]30/10/18[/TD]
[TD]UK[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Cup[/TD]
[TD]30/10/18[/TD]
[TD]US[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Plate[/TD]
[TD]13/05/18[/TD]
[TD]UK[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Plate[/TD]
[TD]19/12/18[/TD]
[TD]UK[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Plate[/TD]
[TD]20/12/18[/TD]
[TD]US[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Plate[/TD]
[TD]25/12/18[/TD]
[TD]UK[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Plate[/TD]
[TD]25/12/18[/TD]
[TD]US[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

I would like a formula that tell me when my next plate is coming in to the UK after a selected date and then in another cell how many units are being delivered.

So for example, if today is 10/5/18 the plates delivered into the UK would be 25.
But if today is 14/5/18 the plates delivered into the UK would be 1000.

I will also have a formula to tell me when the next plate is coming into the US etc.

I have had a really long look at this. Tried a variety of IF AND and IF OR with VLOOKUPs and INDEX MATCH functions. Nothing seemed to work.

Many thanks in advance

Kelly

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G
1	Product	Date	Delivery Location	Units		Product	Plate
2	Cup	30/10/2018	UK	100		Location	UK
3	Cup	30/10/2018	US	50		After Date	17/07/2018
4	Plate	13/05/2018	UK	25		Next Date	19/12/2018
5	Plate	19/12/2018	UK	1000		Units	1000
6	Plate	20/12/2018	US	5
7	Plate	25/12/2018	UK	35
8	Plate	25/12/2018	US	10
Sheet1

Cell Formulas
Range	Formula
G4	{=INDEX($B:$B,SMALL(IF($A:$A=$G$1,IF($B:$B>=$G$3,IF($C:$C=$G$2,ROW($B:$B)))),1))}
G5	{=INDEX($D:$D,SMALL(IF($A:$A=$G$1,IF($B:$B>=$G$3,IF($C:$C=$G$2,ROW($D:$D)))),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[KellyBailey]

Thanks so much.
Works perfectly.