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

OK2020

New Member
Joined
Apr 28, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.

1619621139685.png


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?

Excel Formula:
=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:

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

Attachments

  • 1619619410193.png
    1619619410193.png
    25.9 KB · Views: 5
  • 1619621105312.png
    1619621105312.png
    27.6 KB · Views: 5

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,256
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCDEFGH
1
2Apples01/01/20201.05Apples06/02/20201.05
3Apples15/06/20201.2Oranges04/06/20212.2
4Apples01/01/20211.4Pears03/04/20211.1
5Pears01/01/20201.07
6Pears01/01/20211.1
7Oranges01/01/20202
8Oranges01/01/20212.2
9Oranges01/01/20222.42
10
Lists
Cell Formulas
RangeFormula
H2:H4H2=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

New Member
Joined
Apr 28, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,256
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGH
1
2Apples1.05Apples06/02/2020No match
3Apples15/06/20201.2Oranges04/06/20212.2
4Apples01/01/20211.4Pears03/04/20211.1
5Pears01/01/20201.07
6Pears01/01/20211.1
7Oranges01/01/20202
8Oranges01/01/20212.2
9Oranges01/01/20222.42
10
Lists
Cell Formulas
RangeFormula
H2H2=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:H4H3=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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,132,982
Messages
5,656,220
Members
418,290
Latest member
ArrArkRE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top