Return closest date based on three criteria being true, or return "no date found"

[chris_bosten]

Hello,

All information has been adjusted for anonymised for privacy purposes.

I have data in the following format

Supplier (A1)	Brand (B1)	Grade (C1)	Date of Price Change (D1)	Base Unit (E1)	Currency (F1)	New Unit Price (G1)
Gordon	Flour	W2	1/1/24	Tonne	GBP	5.75
Personage	Cement	CEM II	1/1/24	Tonne	GBP	158.5
Revel	Calcium Carbonate	6.0mm	1/1/24	Tonne	GBP	45.6
Revel	Calcium Carbonate	6.0mm	14/6/24	Tonne	GBP	48.7
Revel	Calcium Carbonate	2.8mm	1/1/24	Tonne	GBP	45.6
Revel	Calcium Carbonate	2.8mm	18/8/24	Tonne	GBP

(Supplier, brand and grade are based on data validation list info from a master list of materials we buy)

I have two boxes where users can enter any dates they wish to look at price change information for a specific product

Period From	Period 2
1/2/24 (any date can be entered) (K5)	16/8/24 (any date can be entered) (L5)

I have three boxes for Supplier, Brand and Grade where users can select which product they wish to look at (all based on data validation)

Supplier (choose from list)	Revel (P3)
Brand (choose from list)	Calcium Carbonate (P5)
Grade (choose from list)	6mm (P7)

I then have a data return table with a single row of information for the user to see price movement activity for their chosen material based on the dates they entered:

Closest Start Date	Start Period Price	Closest End Date	End Period Price	Period Price % Change	Latest Price	Start to Latest Price % Change
1/1/24	45.6	18/8/24	48.7	6.8	48.7	68.7

I have successful array formulas for start period price and end period price based on conditions being met for Supplier, Brand, Grade and Date that looks like this

Start period price: =IFNA(INDEX(G2:G100000,MATCH(1,(D2:D100000=R5)*(A2:A100000=P3)*(B2:B100000=P5)*(C2:C100000=P7),0)),"Price Not Found")
End period price: =IFNA(INDEX(G2:G100000,MATCH(1,(D2:D100000=T5)*(A2:A100000=P3)*(B2:B100000=P5)*(C2:C100000=P7),0)),"No Price Found")

My issue is I only have formulas for closest start date and closest end date, that does not consider Supplier, Brand or Grade. It only considers the dates in Column D:

Closest start date: =INDEX(D2:D100000, MATCH(MIN(ABS(D2:D100000-$K$5)), ABS(D2:D100000-$K$5), 0))
Closest end date: =INDEX(D2:D100000, MATCH(MIN(ABS(D2:D100000-$L$5)), ABS(D2:D100000-$L$5), 0))

For the choices above this returns the following information:

Closest Start Date	Start Period Price	Closest End Date	End Period Price	Period Price % Change	Latest Price	Start to Latest Price % Change
01/01/2024​	£45.60​	18/08/2024​	No Price Found​	#VALUE!​	No Price Found​	#VALUE!​

Because I have selected the 18/8/24 for my "period to", the formulas are returning the closest date as 16/8/24 as it is only looking at the dates column. It then cannot find a price as there is no price listed for Revel Calcium Carbonate 6.0mm for the 18th of August, as that entry is for Calcium Carbonate 2.8mm

I want it to return the 14/6/24 which is the closest date to the 18/8/24 (period to selection) for Revel Calcium Carbonate 6.0mm. Then I think the other formulas will work.

Are you able to help?

 

[chris_bosten]

I have the same question which is perhaps set out in a simpler way here: Nearest date based on three criteria

If I can get an answer to this, I'll be able to adapt it for my example above.

Thanks for any help you can provide!

 

[Kaper]

As you are using Microsoft 365 an easy way to find closest start/end dates will be with Filter function.
You can do it a traditional way, or (to not repeat 3 times FILTER) use LET function, like:

=LET(FLT_DTS,FILTER(D2:D100000,(A2:A100000=P3)*(B2:B100000=P5)*(C2:C100000=P7)),INDEX(FLT_DTS,MATCH(MIN(ABS(FLT_DTS-K5)),ABS(FLT_DTS-K5),0)))

and

=LET(FLT_DTS,FILTER(D2:D100000,(A2:A100000=P3)*(B2:B100000=P5)*(C2:C100000=P7)),INDEX(FLT_DTS,MATCH(MAX(ABS(FLT_DTS-K6)),ABS(FLT_DTS-K6),0)))

Or (shown for end)

=LET(FLT_DTS;FILTER(D2:D100000-K6;(A2:A100000=P3)*(B2:B100000=P5)*(C2:C100000=P7));INDEX(FLT_DTS;MATCH(MAX(ABS(FLT_DTS));ABS(FLT_DTS);0)))+K6

 

[Kaper]

PS.
You can also add special case for no data (on example of first formula) like:

=LET(FLT_DTS,FILTER(D2:D100000,(A2:A100000=P3)*(B2:B100000=P5)*(C2:C100000=P7)),IFERROR(INDEX(FLT_DTS,MATCH(MIN(ABS(FLT_DTS-K5)),ABS(FLT_DTS-K5),0)),"No Date Available"))

And similar approach (again FILTER function, but here we can use 3rd argument of that function to deal with no such data situation) can be used for start period price:

=FILTER(G2:G100000,(A2:A100000=P3)*(B2:B100000=P5)*(C2:C100000=P7)*(D2:D100000=R5),"No Unit Price")

 

[chris_bosten]

Absolutely Amazing, thank you so much!!

 

[chris_bosten]

Hi Mate,

I wonder if you'll know the answer to this one - it's the same sheet.

My formula to return the start period price: Start period price: =IFNA(INDEX(G2:G100000,MATCH(1,(D2:D100000=R5)*(A2:A100000=P3)*(B2:B100000=P5)*(C2:C100000=P7),0)),"Price Not Found") works

However, I have created a new sheet (which performs a different function) where:

R5 is now AS7 in the new sheet
P3 is now G7 in the new sheet
P5 is now H7 in the new sheet
P7 is now I7 in the new sheet

(The date in AS7 is working fine thanks to your formula above - this now reads: =LET(FLT_DTS,FILTER('Materials Price List'!$D$2:$D$100000,('Materials Price List'!$A$2:$A$100000='Procument Guide'!G7)*('Materials Price List'!$B$2:$B$100000='Procument Guide'!H7)*('Materials Price List'!$C$2:$C$100000='Procument Guide'!I7)),IFERROR(INDEX(FLT_DTS,MATCH(MIN(ABS(FLT_DTS-'Procument Guide'!$H$2)),ABS(FLT_DTS-'Procument Guide'!$H$2),0)),"No Date Available")) - although it is returning 00/01/1900 where one of the conditions is blank for some reason, rather than "No Date Available", it's returning the correct date when the conditions match

My formula for start period price in the new sheet is:

=IFNA(INDEX('Materials Price List'!G2:G100000,MATCH(1,('Materials Price List'!D2:D100000='Procument Guide'!AS7)*('Materials Price List'!A2:A100000='Procument Guide'!G7)*('Materials Price List'!B2:B100000='Procument Guide'!H7)*('Materials Price List'!C2:D100000='Procument Guide'!I7),0)),"Price Not Found")


For some reason this is returning "Price Not Found" whereas, on the original sheet it returns 5.50.

Do you have any idea why it's returning price not found?

 

[Kaper]

00/01/1900 means just 0 - only formatted as date. it woulkd be a good idea to analyse how it happens using Function Evaluation, but of course it shall be done on a small subset of data - some 5 rows probably. The other option would be to Enforce Error when the result is 0 by division 1/(1/result) would be result if it is non-zero, but error if result is 0.
So the first formula could read:

=LET(FLT_DTS,FILTER('Materials Price List'!$D$2:$D$100000,('Materials Price List'!$A$2:$A$100000='Procument Guide'!G7)*('Materials Price List'!$B$2:$B$100000='Procument Guide'!H7)*('Materials Price List'!$C$2:$C$100000='Procument Guide'!I7)),IFERROR(1/(1/INDEX(FLT_DTS,MATCH(MIN(ABS(FLT_DTS-'Procument Guide'!$H$2)),ABS(FLT_DTS-'Procument Guide'!$H$2),0))),"No Date Available"))

Have you tested also FILTER for finding a price (post #4)?

 

[chris_bosten]

The filter formula worked for finding a price on both sheets! I think that's cracked it!

I can't thank you enough. I really need to study these formulas more - I'll have a look at some of the articles here. I can't thank you enough, you've really helped me out.

Best wishes
Chris

 

[chris_bosten]

Hi Kaper, sorry to trouble you again as you've helped me so much already, but any idea why this formula is returning "0" (to the last row in the sheet) rather than "No Material Found" in cell J2?