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

[frankieung]

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)

	A	B	C
1	Purchase Dates	Product	Price
2	January 1, 2022	Condensed Milk	1
3	January 1, 2022	Tortilla	0.5
4	January 1, 2022	Chicken Breast	2.5
5	February 1, 2022	Cheese	1.2
6	February 1, 2022	Chicken Breast	2.7
7	March 1, 2022	Cheese	1.3
8	March 1, 2022	Chicken Breast	3
9	March 1, 2022	Condensed Milk	1.25
10	April 1, 2022	Tortilla	0.7

I have used the following formulas obtaining different results.

Formula 1.

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

	A	B	C	D	E
1	Date Order	January 10, 2022	February 1, 2022	March 12, 2022	April 4, 2022
2	Condensed Milk	1	1	1	1
3	Tortilla	0.5	0.5	0.5	0.5
4	Chicken Breast	2.5	2.5	2.5	2.5
5	Cheese	N/A	1.2	1.3	1.3

Formula 2.

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

	A	B	C	D	E
1	Date Order	January 10, 2022	February 1, 2022	March 12, 2022	April 4, 2022
2	Condensed Milk	1.25	1.25	N/A	N/A
3	Tortilla	0.7	0.7	0.7	N/A
4	Chicken Breast	2.7	2.7	N/A	N/A
5	Cheese	1.2	1.2	N/A	N/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)

	A	B	C	D	E
1	Date Order	January 10, 2022	February 1, 2022	March 12, 2022	April 4, 2022
2	Condensed Milk	1	1	1.25	1.25
3	Tortilla	0.5	0.5	0.5	0.7
4	Chicken Breast	2.5	2.7	1.25	1.25
5	Cheese	None Available	1.2	1.3	1.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.

 

[JamesCanale]

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	Purchase Dates	Product	Price					1/1/2022	2/1/2022	3/1/2022	4/1/2022
2	2	1-Jan-22	Condensed Milk	$1.00				Condensed Milk	$1.00	$1.00	$1.25	$1.25
3	3	1-Jan-22	Tortilla	$0.50				Tortilla	$0.50	$0.50	$0.50	$0.70
4	4	1-Jan-22	Chicken Breast	$2.50				Chicken Breast	$2.50	$2.70	$3.00	$3.00
5	5	1-Feb-22	Cheese	$1.20				Cheese	None Available	$1.20	$1.30	$1.30
6	6	1-Feb-22	Chicken Breast	$2.70
7	7	1-Mar-22	Cheese	$1.30
8	8	1-Mar-22	Chicken Breast	$3.00
9	9	1-Mar-22	Condensed Milk	$1.25
10	10	1-Apr-22	Tortilla	$0.70
Sheet3

Cell Formulas
Range	Formula
I1:L1	I1	=TRANSPOSE(UNIQUE(B2:B10))
H2:H5	H2	=UNIQUE(C2:C10)
I2:I5	I2	=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=I$1)),"None Available")
J2:L5	J2	=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=J$1)),I2)
Dynamic array formulas.

 

[frankieung]

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	Purchase Dates	Product	Price					1/1/2022	2/1/2022	3/1/2022	4/1/2022
2	2	1-Jan-22	Condensed Milk	$1.00				Condensed Milk	$1.00	$1.00	$1.25	$1.25
3	3	1-Jan-22	Tortilla	$0.50				Tortilla	$0.50	$0.50	$0.50	$0.70
4	4	1-Jan-22	Chicken Breast	$2.50				Chicken Breast	$2.50	$2.70	$3.00	$3.00
5	5	1-Feb-22	Cheese	$1.20				Cheese	None Available	$1.20	$1.30	$1.30
6	6	1-Feb-22	Chicken Breast	$2.70
7	7	1-Mar-22	Cheese	$1.30
8	8	1-Mar-22	Chicken Breast	$3.00
9	9	1-Mar-22	Condensed Milk	$1.25
10	10	1-Apr-22	Tortilla	$0.70
Sheet3

Cell Formulas
Range	Formula
I1:L1	I1	=TRANSPOSE(UNIQUE(B2:B10))
H2:H5	H2	=UNIQUE(C2:C10)
I2:I5	I2	=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=I$1)),"None Available")
J2:L5	J2	=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
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	Purchase Dates	Product	Price			Example 1	Order Date	10-Jan-22	1-Feb-22	12-Mar-22	4-Jan-22
2	2	1-Jan-22	Condensed Milk	1				Condensed Milk	None Available	None Available	None Available	None Available
3	3	1-Jan-22	Tortilla	0.5				Tortilla	None Available	None Available	None Available	None Available
4	4	1-Jan-22	Chicken Breast	2.5				Chicken Breast	None Available	2.7	None Available	None Available
5	5	1-Feb-22	Cheese	1.2				Cheese	None Available	1.2	None Available	None Available
6	6	1-Feb-22	Chicken Breast	2.7
7	7	1-Mar-22	Cheese	1.3			Example 2	Order Date	1-Jan-22	2-Feb-22	3-Mar-22	4-Jan-22
8	8	1-Mar-22	Chicken Breast	3				Condensed Milk	1	1	1	1
9	9	1-Mar-22	Condensed Milk	1.25				Tortilla	0.5	0.5	0.5	0.5
10	10	1-Apr-22	Tortilla	0.7				Chicken Breast	2.5	2.5	2.5	2.5
11								Cheese	None Available	None Available	None Available	None Available
12
13							Desired	Date Order	10-Jan-22	1-Feb-22	12-Mar-22	4-Apr-22
14								Condensed Milk	1	1	1.25	1.25
15								Tortilla	0.5	0.5	0.5	0.7
16								Chicken Breast	2.5	2.7	1.25	1.25
17								Cheese	None Available	1.2	1.3	1.3
Sheet1

Cell Formulas
Range	Formula
H2:H5,H8:H11	H2	=UNIQUE($C$2:$C$10)
I2:L5	I2	=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H2)*($B$2:$B$10=I$1)),"None Available")
I8:I11	I8	=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H8)*($B$2:$B$10=I$7)),"None Available")
J8:L11	J8	=IFERROR(FILTER($D$2:$D$10,($C$2:$C$10=$H8)*($B$2:$B$10=J$7)),I8)
Dynamic array formulas.

Thank you again!

 

[JamesCanale]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	Purchase Dates	Product	Price					1/10/2022	2/1/2022	3/12/2022	4/1/2022
2	2	1-Jan-22	Condensed Milk	$1.00				Condensed Milk	$1.00	$1.00	$1.25	$1.25
3	3	1-Jan-22	Tortilla	$0.50				Tortilla	$0.50	$0.50	$0.50	$0.70
4	4	1-Jan-22	Chicken Breast	$2.50				Chicken Breast	$2.50	$2.70	$3.00	$3.00
5	5	1-Feb-22	Cheese	$1.20				Cheese	None Available	$1.20	$1.30	$1.30
6	6	1-Feb-22	Chicken Breast	$2.70
7	7	1-Mar-22	Cheese	$1.30
8	8	1-Mar-22	Chicken Breast	$3.00
9	9	1-Mar-22	Condensed Milk	$1.25
10	10	1-Apr-22	Tortilla	$0.70
Sheet3

Cell Formulas
Range	Formula
H2:H5	H2	=UNIQUE(C2:C10)
I2:I5	I2	=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:L5	J2	=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.

 

[frankieung]

Solid!

This works as intended!

I have another question though, should I close this thread and start a new one?

 

[JamesCanale]

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.

 

[frankieung]

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
	A	B	C	D	E	F	G	H
1	Date	Material	Price		Example 1	Order Date	4-Mar-22
2	1-Jan-22	Condensed Milk	1			Material	Condensed Milk
3	1-Jan-22	Tortilla	0.5			Price (Value to Look)	1	< Unexpected Value from C2
4	1-Jan-22	Chicken Breast	2.5		Example 2	Order Date	5-Mar-22
5	1-Feb-22	Cheese	1.2			Material	Chicken Breast
6	1-Feb-22	Chicken Breast	2.7			Price (Value to Look)	2.5	< Unexpected Value from C4
7	1-Mar-22	Cheese	1.3
8	1-Mar-22	Chicken Breast	3		Desired 1	Order Date	4-Mar-22
9	1-Mar-22	Condensed Milk	1.25			Material	Condensed Milk
10	1-Apr-22	Tortilla	0.7			Price (Value to Look)	1.25	< Expected Value from C9
11					Desired 2	Order Date	5-Mar-22
12						Material	Chicken Breast
13						Price (Value to Look)	3	< Expected Value from C8
Thread 2

Cell Formulas
Range	Formula
G3,G6	G3	=INDEX($C$2:$C$10,MATCH(1,(($B$2:$B$10=G2)*($A$2:$A$10<=G1)),0))

Thank you James!

 

[frankieung]

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.

Mr Excel.xlsx
	A	B	C	D	E	F	G
1	Date	Material	Price		Example 1	Order Date	1-Feb-22
2	1-Jan-22	Condensed Milk	1			Material	Tortilla
3	1-Jan-22	Tortilla	0.5			Price (Value to Look)	None Available
4	1-Jan-22	Chicken Breast	2.5
5	1-Feb-22	Cheese	1.2		Desired 1	Order Date	1-Feb-22
6	1-Feb-22	Chicken Breast	2.7			Material	Tortilla
7	1-Mar-22	Cheese	1.3			Price (Value to Look)	0.5
8	1-Mar-22	Chicken Breast	3
9	1-Mar-22	Condensed Milk	1.25
10	1-Apr-22	Tortilla	0.7
Thread 2 Thread 1 Formula

Cell Formulas
Range	Formula
G3	G3	=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")

 

[JamesCanale]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	Purchase Dates	Product	Price					1/1/2022	2/1/2022	3/5/2022	4/1/2022
2	2	1-Jan-22	Condensed Milk	$1.00				Condensed Milk	1	1	1.25	1.25
3	3	1-Jan-22	Tortilla	$0.50				Tortilla	0.5	0.5	0.5	0.7
4	4	1-Jan-22	Chicken Breast	$2.50				Chicken Breast	2.5	2.7	3	3
5	5	1-Feb-22	Cheese	$1.20				Cheese	None Available	1.2	1.3	1.3
6	6	1-Feb-22	Chicken Breast	$2.70
7	7	1-Mar-22	Cheese	$1.30
8	8	1-Mar-22	Chicken Breast	$3.00
9	9	1-Mar-22	Condensed Milk	$1.25
10	10	1-Apr-22	Tortilla	$0.70
Sheet3

Cell Formulas
Range	Formula
H2:H5	H2	=UNIQUE(C2:C10)
I2:L5	I2	=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.

 

[frankieung]

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
	B	C	D	E	F	G	H
1	Date	Material	Price		Example 1	Order Date	24-Jan-22
2	1-Jan-22	Condensed Milk	1			Material	Chicken Breast
3	1-Jan-22	Tortilla	0.5			Price (Value to Look)	2.5
4	1-Jan-22	Chicken Breast	2.5
5	1-Feb-22	Cheese	1.2		Example 2	Order Date	3-Feb-22
6	1-Feb-22	Chicken Breast	2.7			Material	Chicken Breast
7	1-Mar-22	Cheese	1.3			Price (Value to Look)	2.7
8	1-Mar-22	Chicken Breast	3
9	1-Mar-22	Condensed Milk	1.25		Example 2	Order Date	10-Mar-22
10	1-Apr-22	Tortilla	0.7			Material	Chicken Breast
11						Price (Value to Look)	3
SOLUTION!!!

Cell Formulas
Range	Formula
H3	H3	=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")
H7	H7	=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")
H11	H11	=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")