Find invoice price based on location, item#, effective start and end date

[phass001]

Hello everyone, having some difficulty finding a formula to meet all the criteria. I have spent some time searching and appreciate any help on this ask. I am trying to find the corresponding price, based on location, item, and between the date range. From the picture below, I am trying to write a formula in cell J2 to get the correct price $23.25. Thank you!

Book4
	A	B	C	D	E	F	G	H	I	J
1	Location	Item#	Start Date	End Date	Price		Location	Item#	Invoice Date	Price
2	BEN	35135	1/1/2021	12/15/2022	$ 23.00		BEN	35135	10/31/2023	$ 23.25
3	SIG	35135	1/1/2021	12/15/2022	$ 23.50
4	LAX	35135	1/1/2021	12/15/2022	$ 24.00
5	BEN	35135	2/16/2023	11/15/2023	$ 23.25
6	SIG	35135	2/16/2023	11/15/2023	$ 23.75
7	LAX	35135	2/16/2023	11/15/2023	$ 24.50
8	BEN	49999	2/16/2023	11/15/2023	$ 28.00
9	SIG	49999	2/16/2023	11/15/2023	$ 28.25
10	LAX	49999	2/16/2023	11/15/2023	$ 28.95
11	BEN	35135	12/16/2022	2/15/2023	$ 23.10
12	SIG	35135	12/16/2022	2/15/2023	$ 23.25
13	LAX	35135	12/16/2022	2/15/2023	$ 24.15
Sheet4

 

[Trebor76]

Hi phass001,

Welcome to MrExcel!!

Based on the formula from here this will do the job for you:

=INDEX($E$2:$E$13,MATCH(1,(G2=$A$2:$A$13)*(H2=$B$2:$B$13)*(I2>=$C$2:$C$13)*(I2<=$D$2:$D$13),0))

Regards,

Robert

 

[phass001]

Hi phass001,

Welcome to MrExcel!!

Based on the formula from here this will do the job for you:

=INDEX($E$2:$E$13,MATCH(1,(G2=$A$2:$A$13)*(H2=$B$2:$B$13)*(I2>=$C$2:$C$13)*(I2<=$D$2:$D$13),0))

Regards,

Robert

Thank you Robert, appreciate the quick reply. The formula does work, however when I am using the formula in my larger dataset, and the file has become unresponsive and is stuck on calculating threads. Is there any other workaround you may suggest. Thank you

 

[Trebor76]

Is there any other workaround you may suggest.

No I can't I'm afraid but hopefully someone on the forum can

It is an array formula which are notorious for slowing Excel down so make sure you don't reference entire columns but the range you're working with as I have done which may help.

 

[Fluff]

How about

=FILTER(E2:E10000,(A2:A10000=G2)*(B2:B10000=H2)*(C2:C10000<=I2)*(D2:D10000>=I2))