Lookup on basis of three criterias

[khawarameer]

Dear All,

i have a table with 04 columns [Deal_No, Deal_Amount, Start_Date, End_Date] i need to repeat the amount of the deals from start date to end date in specific range of dates, with Deal_no are placed horizontally. The formant of tables is as following.


SOURCE TABLE

DEAL NO <tbody> </tbody>	DEAL AMOUNT <colgroup><col width="116"></colgroup><tbody> </tbody>	START DATE <colgroup><col width="140"></colgroup><tbody> </tbody>	END DATE <colgroup><col width="107"></colgroup><tbody> </tbody>
DEAL 1 <colgroup><col width="92"></colgroup><tbody> </tbody>	4,000,000.00 <colgroup><col></colgroup><tbody> </tbody>	11/12/2014 <colgroup><col width="140"></colgroup><tbody> </tbody>	22/12/2014 <colgroup><col width="107"></colgroup><tbody> </tbody>
DEAL 2 <colgroup><col width="92"></colgroup><tbody> </tbody>	2,000,000.00 <colgroup><col></colgroup><tbody> </tbody>	11/12/2014 <colgroup><col width="140"></colgroup><tbody> </tbody>	24/12/2014 <colgroup><col width="107"></colgroup><tbody> </tbody>
DEAL 3 <colgroup><col width="92"></colgroup><tbody> </tbody>	3,000,000.00 <colgroup><col></colgroup><tbody> </tbody>	11/12/2014 <colgroup><col width="140"></colgroup><tbody> </tbody>	26/12/2014 <colgroup><col width="107"></colgroup><tbody> </tbody>
DEAL 4 <colgroup><col width="92"></colgroup><tbody> </tbody>	3,000,000.00 <colgroup><col></colgroup><tbody> </tbody>	11/12/2014 <colgroup><col width="140"></colgroup><tbody> </tbody>	27/12/2014 <colgroup><col width="107"></colgroup><tbody> </tbody>
DEAL 5 <colgroup><col width="92"></colgroup><tbody> </tbody>	3,300,000.00 <colgroup><col></colgroup><tbody> </tbody>	11/12/2014 <colgroup><col width="140"></colgroup><tbody> </tbody>	28/12/2014 <colgroup><col width="107"></colgroup><tbody> </tbody>

<tbody>
</tbody>

RESULT TABLE

Date	DEAL 1	DEAL 2	DEAL 3	DEAL 4	DEAL 5
10/12/2014
11/12/2014
12/12/2014
13/12/2014
14/12/2014
15/12/2014
16/12/2014
17/12/2014
18/12/2014
19/12/2014
20/12/2014
21/12/2014
22/12/2014
23/12/2014
24/12/2014
25/12/2014
26/12/2014
27/12/2014
28/12/2014
29/12/2014
30/12/2014
31/12/2014
01/01/2015
02/01/2015

<tbody>
</tbody>

Can any one help me in regards.

Khawar

 

[Mackers]

Hi khawarameer

If I'm correct you want the value in the DEAL AMOUNT column to be present in the Deal 1 column if the corresponding Date is within the bounds for Deal 1 specified by your Start Date and End Date.

In the Deal 1 cell next to 10/12/2014, I would put the following:

=sumifs(DealAmountArray, DealNoArray, B$1, StartDateArray, "<="&$A1, EndDateArray, ">="&$A1)

This is where your date is in B1 and the text "Deal 1" is in A1.

Edit: to clarify, when I say DealAmountArray I mean something like Sheet1!B2:B5 if your DealAmounts are in Sheet1!B2:B5.

Hope that helps

Mackers

 

[khawarameer]

Hi khawarameer

If I'm correct you want the value in the DEAL AMOUNT column to be present in the Deal 1 column if the corresponding Date is within the bounds for Deal 1 specified by your Start Date and End Date.

In the Deal 1 cell next to 10/12/2014, I would put the following:

=sumifs(DealAmountArray, DealNoArray, B$1, StartDateArray, "<="&$A1, EndDateArray, ">="&$A1)

This is where your date is in B1 and the text "Deal 1" is in A1.

Edit: to clarify, when I say DealAmountArray I mean something like Sheet1!B2:B5 if your DealAmounts are in Sheet1!B2:B5.

Hope that helps

Mackers

Thanks for the help sir.

It worked fine.

best regards

Khawar