Lookup on basis of three criterias

khawarameer

Board Regular
Joined
Jun 2, 2009
Messages
151
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Mackers

Well-known Member
Joined
Nov 4, 2013
Messages
536
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

Board Regular
Joined
Jun 2, 2009
Messages
151
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,644
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top