Find product price based on date range in lookup table (only 'From Date' in lookup range)

[OK2020]

Hi All !

First post here, but have found solutions on this board to 100s of problems over the years. Thank you all, very, very much!

I need to find the price of a product based on the delivery date and product name.

I think it can be done with SUMIFS, but I'll have to add a "Price To" date. Additionally, I think, I may also have to divide by COUNTIFS to allow for an error/double-up in the price table?

=MAXIFS(B3:B10, B3:B10, "<="&G3)

works great but only returns the dates, not the corresponding price...

The data is not sorted and array formulas are not an option, because they are difficult to understand by the multiple users (including me). Any thoughts....?

For those of you who came across this post looking to solve a similar problem, the below may be helpful:

Summing for Date Ranges

Hello Excel Guru's, I cant seem to sum the costs associated with these date ranges. Grateful for your assistance. thank you in advance. There are 2 sheets. Please scroll down for the date ranges. I have this in one sheet: 1/2/2020 11/29/2030 1/31/2026 1/31/2046 $ 2.00 $...

www.mrexcel.com

How to obtain MAXIF VALUE and it's corresponding cell?

A 27/06/2018 B Deadlift C 1 D 50 E 8 27/06/2018 Deadlift 2 110 5 27/06/2018 Deadlift 3 130 5 27/06/2018 Deadlift 4 130 4 27/06/2018 Deadlift 5 120 5 28/06/2018 Lat Pulldown 1 50 10 28/06/2018 Lat Pulldown 2 50 10 28/06/2018 Lat Pulldown 3 55 7 28/06/2018 Lat Pulldown 4 50 9...

www.mrexcel.com

P.S. The above is an example, I don't have any fruit to sell!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2	Apples	01/01/2020	1.05			Apples	06/02/2020	1.05
3	Apples	15/06/2020	1.2			Oranges	04/06/2021	2.2
4	Apples	01/01/2021	1.4			Pears	03/04/2021	1.1
5	Pears	01/01/2020	1.07
6	Pears	01/01/2021	1.1
7	Oranges	01/01/2020	2
8	Oranges	01/01/2021	2.2
9	Oranges	01/01/2022	2.42
10
Lists

Cell Formulas
Range	Formula
H2:H4	H2	=FILTER($C$2:$C$10,($A$2:$A$10=F2)*($B$2:$B$10=MAXIFS($B$2:$B$10,$B$2:$B$10,"<="&G2,$A$2:$A$10,F2)))

 

[OK2020]

Hi Fluff! Thank you for your answer, it works!

The only disadvantage is that deleting the latest date for a product (say C3) still produces the same answer (1.05). While this looks right (maybe excel treats a blank as the first excel date), I think I would want to return an error or 0, so as to raise a flag something is wrong.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2	Apples		1.05			Apples	06/02/2020	No match
3	Apples	15/06/2020	1.2			Oranges	04/06/2021	2.2
4	Apples	01/01/2021	1.4			Pears	03/04/2021	1.1
5	Pears	01/01/2020	1.07
6	Pears	01/01/2021	1.1
7	Oranges	01/01/2020	2
8	Oranges	01/01/2021	2.2
9	Oranges	01/01/2022	2.42
10
Lists

Cell Formulas
Range	Formula
H2	H2	=FILTER($C$2:$C$10,($B$2:$B$10<>"")*($A$2:$A$10=F2)*($B$2:$B$10=MAXIFS($B$2:$B$10,$B$2:$B$10,"<="&G2,$A$2:$A$10,F2)),"No match")
H3:H4	H3	=FILTER($C$2:$C$10,($A$2:$A$10=F3)*($B$2:$B$10=MAXIFS($B$2:$B$10,$B$2:$B$10,"<="&G3,$A$2:$A$10,F3)))