Help finding a value with two conditions, one of them being a date and the other being text

frankieung

New Member
Joined
Nov 16, 2022
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
Hi,

I have a question and would kindly ask for your help to figure this out.

Table 1, represents the date in which each product was acquired and the price paid for it.

I want to perform a search in this table so it returns the price of a product to its closest registered date.

TABLE 1 (Index)

ABC
1Purchase DatesProductPrice
2January 1, 2022Condensed Milk1
3January 1, 2022Tortilla0.5
4January 1, 2022Chicken Breast2.5
5February 1, 2022Cheese1.2
6February 1, 2022Chicken Breast2.7
7March 1, 2022Cheese1.3
8March 1, 2022Chicken Breast3
9March 1, 2022Condensed Milk1.25
10April 1, 2022Tortilla0.7

I have used the following formulas obtaining different results.

Formula 1.
Excel Formula:
=INDEX('Table 1'!$C$2:$C$10,MATCH(1,(('Table 1'!$B$2:$B$10='Table 2'!$A2)*('Table 1'!$A$2:$A$11<='Table 2'!B$1)),0))

Result
TABLE 2 (Returned)
ABCDE
1Date OrderJanuary 10, 2022February 1, 2022March 12, 2022April 4, 2022
2Condensed Milk1111
3Tortilla0.50.50.50.5
4Chicken Breast2.52.52.52.5
5CheeseN/A1.21.31.3

Formula 2.
Excel Formula:
=INDEX('Table 1'!$C$2:$C$10,MATCH(1,(('Table 1'!$B$2:$B$10='Table 2'!$A2)*('Table 1'!$A$2:$A$11>='Table 2'!B$1)),0))

Result
TABLE 2 (Returned)
ABCDE
1Date OrderJanuary 10, 2022February 1, 2022March 12, 2022April 4, 2022
2Condensed Milk1.251.25N/AN/A
3Tortilla0.70.70.7N/A
4Chicken Breast2.72.7N/AN/A
5Cheese1.21.2N/AN/A

----

Table 3 is the desired result I am trying to achieve, if you look at Table 1 Tortilla has 2 purchase dates (January and April), I want the price to remain constant until updated, so it's then reflected in Table 3, Cell E3, same goes, for example, Chicken Breast which has 3 purchase dates, (January, February and March), on April it displays the same value as March since there is no new purchase dates.

TABLE 3 (Desired)

ABCDE
1Date OrderJanuary 10, 2022February 1, 2022March 12, 2022April 4, 2022
2Condensed Milk111.251.25
3Tortilla0.50.50.50.7
4Chicken Breast2.52.71.251.25
5CheeseNone Available1.21.31.3

The purpose of all this is to create a price tracker for raw materials, with the idea that if any of these raw materials are used in production they can present the closest value to estimate the product cost.

Thank you for taking from your time to read all of this, and try to help me figure this out.

I have looked everywhere but the examples are vague, either using one condition or not using duplicate values as is my case.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
MrExcelPlayground14.xlsx
ABCDEFGHIJKL
11Purchase DatesProductPrice1/1/20222/1/20223/1/20224/1/2022
221-Jan-22Condensed Milk$1.00Condensed Milk$1.00$1.00$1.25$1.25
331-Jan-22Tortilla$0.50Tortilla$0.50$0.50$0.50$0.70
441-Jan-22Chicken Breast$2.50Chicken Breast$2.50$2.70$3.00$3.00
551-Feb-22Cheese$1.20CheeseNone Available$1.20$1.30$1.30
661-Feb-22Chicken Breast$2.70
771-Mar-22Cheese$1.30
881-Mar-22Chicken Breast$3.00
991-Mar-22Condensed Milk$1.25
10101-Apr-22Tortilla$0.70
Sheet3
Cell Formulas
RangeFormula
I1:L1I1=TRANSPOSE(UNIQUE(B2:B10))
H2:H5H2=UNIQUE(C2:C10)
I2:I5I2=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=I$1)),"None Available")
J2:L5J2=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=J$1)),I2)
Dynamic array formulas.
 
Upvote 0
MrExcelPlayground14.xlsx
ABCDEFGHIJKL
11Purchase DatesProductPrice1/1/20222/1/20223/1/20224/1/2022
221-Jan-22Condensed Milk$1.00Condensed Milk$1.00$1.00$1.25$1.25
331-Jan-22Tortilla$0.50Tortilla$0.50$0.50$0.50$0.70
441-Jan-22Chicken Breast$2.50Chicken Breast$2.50$2.70$3.00$3.00
551-Feb-22Cheese$1.20CheeseNone Available$1.20$1.30$1.30
661-Feb-22Chicken Breast$2.70
771-Mar-22Cheese$1.30
881-Mar-22Chicken Breast$3.00
991-Mar-22Condensed Milk$1.25
10101-Apr-22Tortilla$0.70
Sheet3
Cell Formulas
RangeFormula
I1:L1I1=TRANSPOSE(UNIQUE(B2:B10))
H2:H5H2=UNIQUE(C2:C10)
I2:I5I2=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=I$1)),"None Available")
J2:L5J2=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=J$1)),I2)
Dynamic array formulas.

Hi James!

Thank you for the reply, I appreciate your solution, however the problem with this solution is that if my Order date is not the same as the Purchase Dates, it will return me a None Available Error.

In summary. if the Order Dates don't equally match Purchase dates it will provide a different result, instead of, Order Dates referencing Purchase Dates to provide the closest price (less or equal than) such Purchase Dates row has in its Price Column

So if I make a purchase let's say on February 1st 2022, and have an Order Placed on February 7, the values for February 1st should be displayed instead of January 1st 2022.

"Order dates are meant to be in the future but thinking about this now, if an order is placed BEFORE doing the purchase, then Table 1 dates would be in the future whereas Table 2 Dates would be in the past. But the idea is that if an Order is placed you can get a quote referencing to previous Purchase Dates depending on product, independently if the Purchase Date for that Order is done in the future."

A table is worth a million words.
Book1.xlsx
ABCDEFGHIJKL
11Purchase DatesProductPriceExample 1Order Date10-Jan-221-Feb-2212-Mar-224-Jan-22
221-Jan-22Condensed Milk1Condensed MilkNone AvailableNone AvailableNone AvailableNone Available
331-Jan-22Tortilla0.5TortillaNone AvailableNone AvailableNone AvailableNone Available
441-Jan-22Chicken Breast2.5Chicken BreastNone Available2.7None AvailableNone Available
551-Feb-22Cheese1.2CheeseNone Available1.2None AvailableNone Available
661-Feb-22Chicken Breast2.7
771-Mar-22Cheese1.3Example 2Order Date1-Jan-222-Feb-223-Mar-224-Jan-22
881-Mar-22Chicken Breast3Condensed Milk1111
991-Mar-22Condensed Milk1.25Tortilla0.50.50.50.5
10101-Apr-22Tortilla0.7Chicken Breast2.52.52.52.5
11CheeseNone AvailableNone AvailableNone AvailableNone Available
12
13DesiredDate Order10-Jan-221-Feb-2212-Mar-224-Apr-22
14Condensed Milk111.251.25
15Tortilla0.50.50.50.7
16Chicken Breast2.52.71.251.25
17CheeseNone Available1.21.31.3
Sheet1
Cell Formulas
RangeFormula
H2:H5,H8:H11H2=UNIQUE($C$2:$C$10)
I2:L5I2=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=I$1)),"None Available")
I8:I11I8=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H8)*($B$2:$B$10=I$7)),"None Available")
J8:L11J8=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H8)*($B$2:$B$10=J$7)),I8)
Dynamic array formulas.


Thank you again!
 
Upvote 0
I see... I didn't notice the distinction between order and purchase dates. This one looks up the last purchase date from the order date. Perhaps this:

MrExcelPlayground14.xlsx
ABCDEFGHIJKL
11Purchase DatesProductPrice1/10/20222/1/20223/12/20224/1/2022
221-Jan-22Condensed Milk$1.00Condensed Milk$1.00$1.00$1.25$1.25
331-Jan-22Tortilla$0.50Tortilla$0.50$0.50$0.50$0.70
441-Jan-22Chicken Breast$2.50Chicken Breast$2.50$2.70$3.00$3.00
551-Feb-22Cheese$1.20CheeseNone Available$1.20$1.30$1.30
661-Feb-22Chicken Breast$2.70
771-Mar-22Cheese$1.30
881-Mar-22Chicken Breast$3.00
991-Mar-22Condensed Milk$1.25
10101-Apr-22Tortilla$0.70
Sheet3
Cell Formulas
RangeFormula
H2:H5H2=UNIQUE(C2:C10)
I2:I5I2=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=VLOOKUP(I$1,UNIQUE($B$2:$B$10),1,TRUE))),"None Available")
J2:L5J2=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=VLOOKUP(J$1,UNIQUE($B$2:$B$10),1,TRUE))),I2)
Dynamic array formulas.
 
Upvote 0
Solid!

This works as intended!

I have another question though, should I close this thread and start a new one?
 
Upvote 0
Start a new one if you want anyone else to look at it. If it relies on the same structure here - maybe a tweak - ask away.
 
Upvote 0
So I’m looking for the price on cell G3 and G6, if you look at the Order Dates (in the future), it will not retrieve the closest value, but the first value that it finds for each.

So what I want is basically the same result, but I’m relying on this case on Index Match.

On the table below I display the unexpected results and expected ones.


Mr Excel.xlsx
ABCDEFGH
1DateMaterialPriceExample 1Order Date4-Mar-22
21-Jan-22Condensed Milk1MaterialCondensed Milk
31-Jan-22Tortilla0.5Price (Value to Look)1< Unexpected Value from C2
41-Jan-22Chicken Breast2.5Example 2Order Date5-Mar-22
51-Feb-22Cheese1.2MaterialChicken Breast
61-Feb-22Chicken Breast2.7Price (Value to Look)2.5< Unexpected Value from C4
71-Mar-22Cheese1.3
81-Mar-22Chicken Breast3Desired 1Order Date4-Mar-22
91-Mar-22Condensed Milk1.25MaterialCondensed Milk
101-Apr-22Tortilla0.7Price (Value to Look)1.25< Expected Value from C9
11Desired 2Order Date5-Mar-22
12MaterialChicken Breast
13Price (Value to Look)3< Expected Value from C8
Thread 2
Cell Formulas
RangeFormula
G3,G6G3=INDEX($C$2:$C$10,MATCH(1,(($B$2:$B$10=G2)*($A$2:$A$10<=G1)),0))


Thank you James!
 
Upvote 0
Also, tried using the solution you provided but it doesn't seem to find the value, if the Order Date is not within the same month, then it returns a None Available as defined by the IF Error.

This is trickier than it seems. :confused:

Mr Excel.xlsx
ABCDEFG
1DateMaterialPriceExample 1Order Date1-Feb-22
21-Jan-22Condensed Milk1MaterialTortilla
31-Jan-22Tortilla0.5Price (Value to Look)None Available
41-Jan-22Chicken Breast2.5
51-Feb-22Cheese1.2Desired 1Order Date1-Feb-22
61-Feb-22Chicken Breast2.7MaterialTortilla
71-Mar-22Cheese1.3Price (Value to Look)0.5
81-Mar-22Chicken Breast3
91-Mar-22Condensed Milk1.25
101-Apr-22Tortilla0.7
Thread 2 Thread 1 Formula
Cell Formulas
RangeFormula
G3G3=IFERROR(FILTER($C$2:$C$10,($B$2:$B$10=$G2)*($A$2:$A$10=VLOOKUP(G$1,UNIQUE($A$2:$A$10),1,TRUE))),"None Available")
 
Upvote 0
This might fix all the problems. I got away from looking up, and filtered for all values before the ORDER DATE - and took the most recent one of those (assuming the purchase dates are sorted).
MrExcelPlayground14.xlsx
ABCDEFGHIJKL
11Purchase DatesProductPrice1/1/20222/1/20223/5/20224/1/2022
221-Jan-22Condensed Milk$1.00Condensed Milk111.251.25
331-Jan-22Tortilla$0.50Tortilla0.50.50.50.7
441-Jan-22Chicken Breast$2.50Chicken Breast2.52.733
551-Feb-22Cheese$1.20CheeseNone Available1.21.31.3
661-Feb-22Chicken Breast$2.70
771-Mar-22Cheese$1.30
881-Mar-22Chicken Breast$3.00
991-Mar-22Condensed Milk$1.25
10101-Apr-22Tortilla$0.70
Sheet3
Cell Formulas
RangeFormula
H2:H5H2=UNIQUE(C2:C10)
I2:L5I2=IFERROR(INDEX(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*(I$1>=$B$2:$B$10)),ROWS(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*(I$1>=$B$2:$B$10)))),"None Available")
Dynamic array formulas.
 
Upvote 0
Solution
Sorry for the late reply James.

I tried this and I gotta say.. YOU A GENIUS!!

Thank you so much, I've tested it and it works as intended, I did a tweak but just changed references on formula nothing else.

Mr Excel.xlsx
BCDEFGH
1DateMaterialPriceExample 1Order Date24-Jan-22
21-Jan-22Condensed Milk1MaterialChicken Breast
31-Jan-22Tortilla0.5Price (Value to Look)2.5
41-Jan-22Chicken Breast2.5
51-Feb-22Cheese1.2Example 2Order Date3-Feb-22
61-Feb-22Chicken Breast2.7MaterialChicken Breast
71-Mar-22Cheese1.3Price (Value to Look)2.7
81-Mar-22Chicken Breast3
91-Mar-22Condensed Milk1.25Example 2Order Date10-Mar-22
101-Apr-22Tortilla0.7MaterialChicken Breast
11Price (Value to Look)3
SOLUTION!!!
Cell Formulas
RangeFormula
H3H3=IFERROR(INDEX(FILTER($D$2:$D$27,($C$2:$C$27=$H2)*(H$1>=$B$2:$B$27)),ROWS(FILTER($D$2:$D$27,($C$2:$C$27=$H2)*(H$1>=$B$2:$B$27)))),"None Available")
H7H7=IFERROR(INDEX(FILTER($D$2:$D$27,($C$2:$C$27=$H6)*(H$5>=$B$2:$B$27)),ROWS(FILTER($D$2:$D$27,($C$2:$C$27=$H6)*(H$5>=$B$2:$B$27)))),"None Available")
H11H11=IFERROR(INDEX(FILTER($D$2:$D$27,($C$2:$C$27=$H10)*(H$9>=$B$2:$B$27)),ROWS(FILTER($D$2:$D$27,($C$2:$C$27=$H10)*(H$9>=$B$2:$B$27)))),"None Available")
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

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
Back
Top