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:

Product ID Price Date Need price 2512330 05/07/2016 ??? 2512330 06/07/2016 ??? 2512330 06/13/2016 ??? 2512330 07/20/2016 ??? 2512330 08/27/2016 ??? 2512330 09/22/2016 ??? <tbody> </tbody>

<tbody>
</tbody>

Here is my source table:

Product ID	Current Update	Price	First Update	Price	Second Update	Price	Third Update	Price	Fourth Update	Price	Fifth Update	Price
2143480	5/2/2017	405.60	8/18/2016	375.60	12/23/2015	344.88	5/29/2015	319.08	10/20/2014	292.99	10/1/1996	0.00
2512330	5/2/2017	18.36	12/6/2016	16.70	6/3/2016	15.62	12/1/2015	14.22	5/14/2015	12.94	12/4/2014	11.77
3089421	1/1/2017	7.77	4/1/2016	7.20	8/24/2015	6.67	12/15/2014	6.30	3/17/2014	5.83	8/30/2013	5.31

<tbody>
</tbody>

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
1	Product ID	Price Date	Need price
2	2512330	05/07/2016	14.22
3	2512330	06/07/2016	15.62
4	2512330	06/13/2016	15.62
5	2512330	07/20/2016	15.62
6	2512330	08/27/2016	15.62
7	2512330	09/22/2016	15.62
8
9
10	Product ID	Current Update	Price	First Update	Price	Second Update	Price	Third Update	Price	Fourth Update	Price	Fifth Update	Price
11	2143480	05/02/2017	405.6	8/18/2016	375.6	12/23/2015	344.88	5/29/2015	319.08	10/20/2014	292.99	10/01/1996	0
12	2512330	05/02/2017	18.36	12/06/2016	16.7	06/03/2016	15.62	12/01/2015	14.22	5/14/2015	12.94	12/04/2014	11.77
13	3089421	01/01/2017	7.77	04/01/2016	7.2	8/24/2015	6.67	12/15/2014	6.3	3/17/2014	5.83	8/30/2013	5.31

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula C2 {=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)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Mike Szczesny]

I was able to get the answer 14.22. Can you use this?

=INDEX($B$10:$K$12,MATCH($B$2,INDEX($B$10:$K$12,MATCH($A$2,$A$10:$A$12,0),0),1),0)

<tbody>
</tbody>

Assume A2 is the ID, B2 is the Date. Assume data range is B10:K12