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]

 

[CyrusTheVirus]

Welcome to the forums.

Questions:

1) From where are you pulling the data?
2) What is your current formula?
3) What is your expected results? Please show.

 

[DanishOrange]

Hi thanks for the quick reply.
1. The data keyed in and part of the excel file, original is a mix of sources and methods of pulling it together.
2. =LOOKUP(2,1/($C:$C<=C$9)/($D:$D>=C$9),$E:$E), which only looks at dates and not the other variables of store and product.
3. The variables for the formula are in row 9, with the expected output of 2.00 for those variables (D9) but the formula to get to that is beyond my skill level!!

 

[CyrusTheVirus]

are you able to copy/paste both tables into your reply so we can see the starting and ending points?

 

[DanishOrange]

Hi, struggling to get to post the image of the sheet, even with the add ons (my fault using a mac!), the top row and first column in the first post were to proxy the excel headers, does that help?

 

[DanishOrange]

will try to make the add ons work, but its a Sunday after a Saturday night

 

[CyrusTheVirus]

can't you just copy/paste?

 

[DanishOrange]

[TABLE="width: 530"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]RETAILER[/TD]
[TD]PRODUCT CODE[/TD]
[TD]FROM[/TD]
[TD]TO[/TD]
[TD]PRICE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOBSTORES[/TD]
[TD]PRC1[/TD]
[TD="align: right"]01/01/2010[/TD]
[TD="align: right"]31/12/2015[/TD]
[TD="align: right"] 4.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOBSTORES[/TD]
[TD]PRC2[/TD]
[TD="align: right"]01/01/2010[/TD]
[TD="align: right"]31/12/2015[/TD]
[TD="align: right"] 2.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STEVESTORES[/TD]
[TD]PRC1[/TD]
[TD="align: right"]01/01/2010[/TD]
[TD="align: right"]31/12/2015[/TD]
[TD="align: right"] 3.95[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STEVESTORES[/TD]
[TD]PRC2[/TD]
[TD="align: right"]01/01/2010[/TD]
[TD="align: right"]31/12/2015[/TD]
[TD="align: right"] 1.85[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOBSTORES[/TD]
[TD]PRC1[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]31/12/2020[/TD]
[TD="align: right"] 4.20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOBSTORES[/TD]
[TD]PRC2[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]31/12/2020[/TD]
[TD="align: right"] 2.10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STEVESTORES[/TD]
[TD]PRC1[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]31/12/2020[/TD]
[TD="align: right"] 4.15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STEVESTORES[/TD]
[TD]PRC2[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]31/12/2020[/TD]
[TD="align: right"] 1.95[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SEARCH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RETAILER[/TD]
[TD]PRODUCT CODE[/TD]
[TD]DATE[/TD]
[TD][/TD]
[TD]PRICE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOBSTORES[/TD]
[TD]PRC2[/TD]
[TD="align: right"]26/07/2014[/TD]
[TD][/TD]
[TD="align: right"] 1.85[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]LOOKUP(2,1/(Sheet10!$C:$C<=C$14)/(Sheet10!$D:$D>=C$14),Sheet10!$E:$E)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanishOrange]

Excel 2016 (Mac) 32 bit
	J
7
Sheet10

 

[CyrusTheVirus]

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