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

#### OK2020

##### New Member
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?

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!

#### Attachments

• 1619619410193.png
25.9 KB · Views: 5
• 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
Hi & welcome to MrExcel.
+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

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
+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)))

Replies
1
Views
520
Replies
2
Views
156
Replies
8
Views
150
Replies
1
Views
581
Replies
3
Views
117

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.

### Which adblocker are you using?

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

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