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

	A	B	C	D	E
1	STORE	PRODUCT	FROM	TO	PRICE
2	BOBSTORE	PRC1	01/01/2015	31/12/2017	4.00
3	BOBSTORE	PRC2	01/01/2015	31/12/2017	2.00
4	BOBSTORE	PRC1	01/01/2018	31/12/2025	4.15
5	STEVESTORE	PRC1	01/01/2015	31/12/2018	3.90
6	STEVESTORE	PRC1	01/01/2019	31/12/2020	4.45
7	STEVESTORE	PRC2	01/01/2015	31/12/2020	1.95
8
9	BOBSTORE	PRC2	24/07/2016	OUTPUT??

<tbody>
</tbody>

 

[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]

RETAILER	PRODUCT CODE	FROM	TO	PRICE
BOBSTORES	PRC1	01/01/2010	31/12/2015	4.00
BOBSTORES	PRC2	01/01/2010	31/12/2015	2.00
STEVESTORES	PRC1	01/01/2010	31/12/2015	3.95
STEVESTORES	PRC2	01/01/2010	31/12/2015	1.85
BOBSTORES	PRC1	01/01/2016	31/12/2020	4.20
BOBSTORES	PRC2	01/01/2016	31/12/2020	2.10
STEVESTORES	PRC1	01/01/2016	31/12/2020	4.15
STEVESTORES	PRC2	01/01/2016	31/12/2020	1.95
SEARCH
RETAILER	PRODUCT CODE	DATE		PRICE
BOBSTORES	PRC2	26/07/2014		1.85
	LOOKUP(2,1/(Sheet10!$C:$C<=C$14)/(Sheet10!$D:$D>=C$14),Sheet10!$E:$E)

<colgroup><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[DanishOrange]

Excel 2016 (Mac) 32 bit
	J
7
Sheet10

 

[CyrusTheVirus]

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