Lookup on basis of three criterias

khawarameer

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

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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
152
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,313
Messages
5,836,589
Members
430,438
Latest member
David Gr

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