Extract data based on 3 condition

Kumar Amitouj jain

New Member
Joined
May 13, 2017
Messages
1
Hi

I am a very basic user of excel, I am trying to extract data based on 3 conditions, tried to understand and did what ever was shown but still unable to get a solution any help would be highly appreciated.

Columns P,Q,R are search criterion 's' is total meeting that criterion

need to extract all data from left hand side meeting the search criterion ideally on sheet 2, but can't even make it work on same sheet.

Problem 1: it pulls up data but it is not the right one its always the one in row 2
PROBLEM2 : AND IF I try tocopy it in other cell it comes up with#num error
Dont know if i can attach a sample file but below is copy paste of what i am doing

this is the source data:
execution_Date FrequencyMonthOrdering CustomerStart DateEnd DateDebiting_AcBeneficiary NameBen SortBen AccountAmountOrdering RefBen Ref
1ANNUALLYJanuaryabcd01/11/201301/11/2016123456aaaaa123456789456 ANNUAL INT OF 60340766030002INTEREST
1MONTHLYJanuaryxyz03/05/2006UNTIL FURTHER NOTICE789456bbbbb4565784561231525DAVID K S TSE FOR RENT
1ANNUALLYJanuaryllllll03/11/2014UNTIL FURTHER NOTICE456789xxxxxx96897814785210SPONSAPOLL A/CPZS14-217

<colgroup><col><col span="2"><col><col><col><col span="4"><col><col><col></colgroup><tbody>
</tbody>


this is the search criterio:

execution_Date MonthFrequencyCount # that Pass all Hurdles
1AprilMonthly1

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


This is the formula i tried using:

=IF(ROWS(P$5:P5)<=$S$2,INDEX(INDIRECT(P$4),SMALL(IF(execution_Date=$P$2,IF(Frequency=$Q$2,IF(Month=Q2,ROW(Frequency)-ROW($C$4)+1))),ROWS(P$4:P4))),"")

<colgroup><col width="113"></colgroup><tbody>
</tbody>


I always get this result only:
execution_Date FrequencyOrdering_CustomerStart_DateEnd_DateDebiting_AcBeneficiary_NameBen_SortBen_AccountAmountOrdering_RefBen_Ref
1MONTHLYxyz38840UNTIL FURTHER NOTICE789456bbbbb4565784561231525DAVID K S TSE FOR RENT

<colgroup><col style="mso-width-source:userset;mso-width-alt:4132;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:5851;width:120pt" width="160"> <col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:3328;width:68pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <col style="width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="width:47pt" width="63" span="2"> <col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> </colgroup><tbody>
</tbody>


please help
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,293
Members
418,187
Latest member
polks111

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