Find a value based on one criteria and two match ranges

0
votes

I have two data sources:

Source 1 contains product id and price date, need to find the price that corresponds with the date from Source 2 based on product id and between two date ranges because there is no exact date match. Here are the tables:

Source 1

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  ???

Source 2:

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

Thank in advance for your help.

octord

1
excel-formula

1 Answers

0
votes

Use this formula:

=INDEX(A:M,MATCH(P2,A:A,0),AGGREGATE(15,6,COLUMN(INDEX(A:M,MATCH(P2,A:A,0),0))/((INDEX(A:M,MATCH(P2,A:A,0),0)<=Q2)*(ISEVEN(COLUMN(INDEX(A:M,MATCH(P2,A:A,0),0))))),1)+1)

You may need to change the ISEVEN to ISODD depending on the column in which the dates are found.

enter image description here