Lookup / index last date and matching code column

Number1One

New Member
Joined
Mar 1, 2019
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Haven't been able to solve the following (Green cells), help appreciated.

Use product code from table 1 to find price in table 2, but needs to be the last effective date before or equal to the week commencing date.

1636056060136.png


Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi Number1One,

Does this do what you want?

Number1One.xlsx
ABCDEFGHI
1Week CommencingProductProduct NamePrice to findEffective DateCodePrice
206-Sep-211234201-Aug-2112342
306-Sep-215678306-Sep-2156783
413-Sep-211234411-Sep-2112344
513-Sep-215678314-Sep-2156785
601-Jan-211234No price
7 
Sheet1
Cell Formulas
RangeFormula
D2:D7D2=IF(A2="","",IFERROR(INDEX($I$2:$I$9999,AGGREGATE(15,6,ROW($G$2:$G$9999)-ROW($G$1)/(($H$2:$H$9999=B2)*($G$2:$G$9999=AGGREGATE(14,6,$G$2:$G$9999/(($H$2:$H$9999=B2)*($G$2:$G$9999<=A2)),1))),1)),"No price"))
 
Upvote 0
Solution
? - Feel like this is the moments this emoji was made for.

Awesome Toadstool, thanks just applied it to my actual data and it works, Any chance of sharing a sentence or 2 on explaining how it works?
 
Upvote 0
You're welcome!
You may want to update your profile with the version of Excel you use because this would be easier with 365 but I only have Excel 2016 so AGGREGATE is my approach.

The IF and IFERROR are there to handle blank rows or products with no earlier effective dates.
=IF(A2="","",IFERROR(....,"No price"))

The second AGGREGATE uses the 14 option which is LARGE (and a k of 1 to find the largest) so it looks down column G for the largest number, in this case a date, and divides it by the logical result of ($H$2:$H$9999=B2)*($G$2:$G$9999<=A2). So if the row has a code equal to B2 and a date less than or equal to the Week Commencing Date then this results in logical 1*1 which = 1 which divided into the date returns the date. If either is false (0*1, 1*0 or 0*0) then you get a zero returned. So if we look at row 5 the only Effective Date that is <= 13 September is 06-Sep-2021 so that is returned.

The first AGGREGATE doesn't look down a column of values but by row from 2 to 9999. It uses the same divide by to find the row number where the Product=Code and the Effective Date= the 06-Sep-2021 returned by the second AGGREGATE. That is row 3 and the INDEX retrieves the Price from column I.
 
Upvote 0
Thanks, that helps, will still take a couple of reads through though.
Added 365 to profile - only recently though, lots of nice updates from 2013!
 
Upvote 0
As you have 365, another option is
Excel Formula:
=INDEX(SORT(FILTER($G$2:$I$100,($H$2:$H$100=B2)*($G$2:$G$100<=A2),{"","","No Price"}),1,-1),1,3)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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