Lookup All Corresponding Values Match Criteria In Adjacent Column Array Formula

[d380013]

Dear friends

i want array formula help in my project

pls refer my sheet @ https://docs.google.com/file/d/0B01x...it?usp=sharing

thanks

dhaval shah

[princ_wns]

Can you please share any sample file ?

Regards
Prince

[LalitPandey87]

Hi Dhaval,

Are you talking about the same which you have already posted at below mentioned link

https://groups.google.com/forum/#!to...B1-25-false%5D
https://groups.google.com/forum/#!to...B1-25-false%5D

[LalitPandey87]

Try this and follow the steps:

Select cell S14:S32, press F2 and paste below formuls and confirm it with CSE

=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 <=$T$11)*($E$1:$E$35068 = $S$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),1),"")

Select cell T14:T32, press F2 and paste below formuls and confirm it with CSE

=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 <=$T$11)*($E$1:$E$35068 = $S$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),3),"")

Select cell U14:U32, press F2 and paste below formuls and confirm it with CSE

=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 <=$T$11)*($E$1:$E$35068 = $S$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),4),"")

Select cell V14:V32, press F2 and paste below formuls and confirm it with CSE

=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 <=$T$11)*($E$1:$E$35068 = $S$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),12),"")

Select cell W14:W32 press F2 and paste below formuls and confirm it with CSE

=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068<=$T$11)*($E$1:$E$35068=$S$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068<>0),0,""))*ROW($A$1:$A$35068),""),ROW($A$1:$A$35068)),""),13),"")

Select cell X14:X32, press F2 and paste below formuls and confirm it with CSE

=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 >=$T$11)*($E$1:$E$35068 = $X$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),1),"")

Select cell Y14:Y32, press F2 and paste below formuls and confirm it with CSE

=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 >=$T$11)*($E$1:$E$35068 = $X$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),3),"")

Select cell Z14:Z32, press F2 and paste below formuls and confirm it with CSE

=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 >=$T$11)*($E$1:$E$35068 = $X$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),4),"")

Select cell AA14:AA32, press F2 and paste below formuls and confirm it with CSE

=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068 >=$T$11)*($E$1:$E$35068 = $X$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),12),"")

Select cell AB14:AB32, press F2 and paste below formuls and confirm it with CSE

=IFERROR(INDEX($B$1:$N$35068,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$35068>=$T$11)*($E$1:$E$35068=$X$13)*($B$1:$B$11180 = $T$10)*($M$1:$M$35068<>0),0,""))*ROW($A$1:$A$35068),""),ROW($A$1:$A$35068)),""),13),"")

Hope this is what you are asking for.

[d380013]

Hello Lalitji

its working for one stock only if there is much more Stocks then Formula not working pls refer my sheet

[d380013]

Sir

Pls have a look at https://docs.google.com/file/d/0B01x...it?usp=sharing

in excelsheet pls refer sheet3

if i select any Stocks from S10 the out put is not as per what i want

[LalitPandey87]

I didn't get you. If you drag the old formula it is giving result if it is not correct please share file with the desired output.

[d380013]

Sir ,

i send a detail e-mail for desire output

Thanks

Dhaval Shah

98240 35253

[d380013]

Sir ,

Here with send a download link of my desire out put ................

https://docs.google.com/file/d/0B57o...it?usp=sharing

Thanks

Dhaval Shah

[LalitPandey87]

Have you tried with the new solution which i have given you in my previous post. I have changed it as according to the file which you have just shared.