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.
 
Thanks, I've learned so much watching the pros on here in the last 2 years.
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,224,820
Messages
6,181,159
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