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]

Thanks, I've learned so much watching the pros on here in the last 2 years.

 

[frankieung]

Thanks, I've learned so much watching the pros on here in the last 2 years.

Well I guess I just started doing the same!