Find cell value based on multiple criteria

[octord]

I have a table that contains productid and price date. The price has gone through changes overtime and need to find the price value based on specific date but the this specific date fall between a range of dates.

Here is my work table:

[TABLE="width: 225"]
<tbody>[TR]
[TD][TABLE="width: 225"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Price Date[/TD]
[TD]Need price[/TD]
[/TR]
[TR]
[TD]2512330[/TD]
[TD]05/07/2016[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]2512330[/TD]
[TD]06/07/2016[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]2512330[/TD]
[TD]06/13/2016[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]2512330[/TD]
[TD]07/20/2016[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]2512330[/TD]
[TD]08/27/2016[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]2512330[/TD]
[TD]09/22/2016[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is my source table:

[TABLE="width: 824"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Current Update[/TD]
[TD]Price[/TD]
[TD]First Update[/TD]
[TD]Price[/TD]
[TD]Second Update[/TD]
[TD]Price[/TD]
[TD]Third Update[/TD]
[TD]Price[/TD]
[TD]Fourth Update[/TD]
[TD]Price[/TD]
[TD]Fifth Update[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD="align: right"]2143480[/TD]
[TD="align: right"]5/2/2017[/TD]
[TD="align: right"]405.60[/TD]
[TD="align: right"]8/18/2016[/TD]
[TD="align: right"]375.60[/TD]
[TD="align: right"]12/23/2015[/TD]
[TD="align: right"]344.88[/TD]
[TD="align: right"]5/29/2015[/TD]
[TD="align: right"]319.08[/TD]
[TD="align: right"]10/20/2014[/TD]
[TD="align: right"]292.99[/TD]
[TD="align: right"]10/1/1996[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]2512330[/TD]
[TD="align: right"]5/2/2017[/TD]
[TD="align: right"]18.36[/TD]
[TD="align: right"]12/6/2016[/TD]
[TD="align: right"]16.70[/TD]
[TD="align: right"]6/3/2016[/TD]
[TD="align: right"]15.62[/TD]
[TD="align: right"]12/1/2015[/TD]
[TD="align: right"]14.22[/TD]
[TD="align: right"]5/14/2015[/TD]
[TD="align: right"]12.94[/TD]
[TD="align: right"]12/4/2014[/TD]
[TD="align: right"]11.77[/TD]
[/TR]
[TR]
[TD="align: right"]3089421[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]7.77[/TD]
[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]7.20[/TD]
[TD="align: right"]8/24/2015[/TD]
[TD="align: right"]6.67[/TD]
[TD="align: right"]12/15/2014[/TD]
[TD="align: right"]6.30[/TD]
[TD="align: right"]3/17/2014[/TD]
[TD="align: right"]5.83[/TD]
[TD="align: right"]8/30/2013[/TD]
[TD="align: right"]5.31[/TD]
[/TR]
</tbody>[/TABLE]

You can see that my work table has only productid but multiple price dates, that's where I need to find the price that correspond to that date and unfortunately it falls between a range. I have tried combination of vlookup and match and it did not work, also tried some index/match but no luck.

Any help/direction will greatly appreciated.

Thanks,

octord

 

[AlanY]

try this

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M
Product ID	Price Date	Need price
06/13/2016
07/20/2016
08/27/2016
09/22/2016
Product ID	Current Update	Price	First Update	Price	Second Update	Price	Third Update	Price	Fourth Update	Price	Fifth Update	Price
8/18/2016	12/23/2015	5/29/2015	10/20/2014
5/14/2015
8/24/2015	12/15/2014	3/17/2014	8/30/2013

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2512330[/TD]
[TD="align: right"]05/07/2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]14.22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2512330[/TD]
[TD="align: right"]06/07/2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]15.62[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2512330[/TD]

[TD="bgcolor: #E2EFDA, align: right"]15.62[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2512330[/TD]

[TD="bgcolor: #E2EFDA, align: right"]15.62[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2512330[/TD]

[TD="bgcolor: #E2EFDA, align: right"]15.62[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2512330[/TD]

[TD="bgcolor: #E2EFDA, align: right"]15.62[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2143480[/TD]
[TD="align: right"]05/02/2017[/TD]
[TD="align: right"]405.6[/TD]

[TD="align: right"]375.6[/TD]

[TD="align: right"]344.88[/TD]

[TD="align: right"]319.08[/TD]

[TD="align: right"]292.99[/TD]
[TD="align: right"]10/01/1996[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2512330[/TD]
[TD="align: right"]05/02/2017[/TD]
[TD="align: right"]18.36[/TD]
[TD="align: right"]12/06/2016[/TD]
[TD="align: right"]16.7[/TD]
[TD="align: right"]06/03/2016[/TD]
[TD="align: right"]15.62[/TD]
[TD="align: right"]12/01/2015[/TD]
[TD="align: right"]14.22[/TD]

[TD="align: right"]12.94[/TD]
[TD="align: right"]12/04/2014[/TD]
[TD="align: right"]11.77[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3089421[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]7.77[/TD]
[TD="align: right"]04/01/2016[/TD]
[TD="align: right"]7.2[/TD]

[TD="align: right"]6.67[/TD]

[TD="align: right"]6.3[/TD]

[TD="align: right"]5.83[/TD]

[TD="align: right"]5.31[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=INDEX($B$11:$M$13,MATCH($A2,$A$11:$A$13,0),MATCH(B2,IF($B$10:$M$10<>"Price",INDEX($B$11:$M$13,MATCH(A2,$A$11:$A$13,0),0)),-1)+3)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mike Szczesny]

I was able to get the answer 14.22. Can you use this? [TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"] =INDEX($B$10:$K$12,MATCH($B$2,INDEX($B$10:$K$12,MATCH($A$2,$A$10:$A$12,0),0),1),0)[/TD]
[/TR]
</tbody>[/TABLE]
Assume A2 is the ID, B2 is the Date. Assume data range is B10:K12