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/0B01xhb1Lk7tmUTN2c24zTFdWNzg/edit?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/#!topic/excel-macros/niEdCWl46EI%5B1-25-false%5D
https://groups.google.com/forum/#!topic/excel-macros/6TChQDX0EK0%5B1-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/0B01xhb1Lk7tmUTN2c24zTFdWNzg/edit?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/0B57oFxKylXSFbHVGeVhhdGdOd2c/edit?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. :confused:

[d380013]

sir i am not getting any update

[LalitPandey87]

I mean to say my 2nd post on this thread. :rolleyes:

[d380013]

Yes sir i try it but its not working

[d380013]

sir also send u a file on your e-mail i'd

pls help me out to get solution

[LalitPandey87]

First thing:
I have placed a condition in formula which is:

($M$1:$M$35068 <> 0)

because you told me that you only want to see those records which are not 0 in column name "OPEN_INT"

Second thing:
If you need above conditon as well then you need to select more rows and then press F2 and Press CSE(Ctrl + Shift + Enter) as you are using formula only in range W14:W32 you need to resize it for all results.

If both is not the same what you are asking for then why don't you send me the result for all stocks manually which you want me to get using formula may be it will give me better clearity.

And i am talking about the formula in your file which you just sent me.

[LalitPandey87]

Why don't you use Macro to do this all because using formula it's taking so long to calculate the data as the data is huge. :confused:

[LalitPandey87]

Try this and follow the steps:

Select cell R14:R32, 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 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)),""),3),"")
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)),""),4),"")
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)),""),12),"")
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)),""),13),"")
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 >$S$11)*($E$1:$E$35068 = $W$13)*($B$1:$B$35068=$S$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),1),"")
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 >$S$11)*($E$1:$E$35068 = $W$13)*($B$1:$B$35068 =$S$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),3),"")
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 >$S$11)*($E$1:$E$35068 = $W$13)*($B$1:$B$35068 =$S$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),4),"")
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 >$S$11)*($E$1:$E$35068 = $W$13)*($B$1:$B$35068 =$S$10)*($M$1:$M$35068 <> 0),0,""))*ROW($A$1:$A$35068),""), ROW($A$1:$A$35068)),""),12),"")
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>$S$11)*($E$1:$E$35068=$W$13)*($B$1:$B$35068 =$S$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.
Please make sure you are applying all this formula in the file you have sent me.

Or you can go through the file. i applied all the formulas in the file. I have deleted some data to make it small in size so please paste all your data into the file and then change the formula mode to Automatic.

695