Searching with date range and other variables.

[DanishOrange]

I am wanting to search a price table with a number of columns; store, product, date range (from /to) & price.

I have the elements I of the store , product and exact date within the range an looking to get the price as an output.

I have tried all sorts and can't get a working approach, this is of course a small example table the real one isn't that much bigger (perhaps 1,000 rows).

Any thoughts? thanks for your help

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]STORE[/TD]
[TD]PRODUCT[/TD]
[TD]FROM[/TD]
[TD]TO[/TD]
[TD]PRICE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BOBSTORE[/TD]
[TD]PRC1[/TD]
[TD]01/01/2015[/TD]
[TD]31/12/2017[/TD]
[TD]4.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BOBSTORE[/TD]
[TD]PRC2[/TD]
[TD]01/01/2015[/TD]
[TD]31/12/2017[/TD]
[TD]2.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BOBSTORE[/TD]
[TD]PRC1[/TD]
[TD]01/01/2018[/TD]
[TD]31/12/2025[/TD]
[TD]4.15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]STEVESTORE[/TD]
[TD]PRC1[/TD]
[TD]01/01/2015[/TD]
[TD]31/12/2018[/TD]
[TD]3.90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]STEVESTORE[/TD]
[TD]PRC1[/TD]
[TD]01/01/2019[/TD]
[TD]31/12/2020[/TD]
[TD]4.45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]STEVESTORE[/TD]
[TD]PRC2[/TD]
[TD]01/01/2015[/TD]
[TD]31/12/2020[/TD]
[TD]1.95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]BOBSTORE[/TD]
[TD]PRC2[/TD]
[TD]24/07/2016[/TD]
[TD]OUTPUT??[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanishOrange]

lol, well that doesn't look right. sorry danish, need more info

lol know what you mean, I've tried to post a screen shot but waiting for authorisation, from the first post the top row is the excel column letters and first column is the excel numbers, the formula I've shown is the references to that column and cell references.

I will try again, but it won't let me post a screen shot!

 

[DanishOrange]

[/URL][/IMG]

 

[CyrusTheVirus]

From where are you expecting the output to come?

 

[DanishOrange]

Looking to search the table A1:E9,

Then to find the only record that matched the conditions in cells, A14, B14, C14 (where this is an exact date and the From/To Dates are a range)

and to output the price from column E into cell E14.

 

[DanishOrange]

hi think i've worked it out, don't really like array formulas, so if anyone has a more elegant solution please let me know

{=INDEX($E$2:$E$9,MATCH(1,IF(C14>=$C$2:$C$9,IF(C14<=$D$2:$D$9,IF(A14=$A$2:$A$9,IF(B14=$B$2:$B$9,1)))),0))}