Lookup All Corresponding Values Match Criteria In Adjacent Column Array Formula

Printable View

04-18-2013, 01:06 PM
d380013

Lookup All Corresponding Values Match Criteria In Adjacent Column Array Formula

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
04-18-2013, 03:23 PM
princ_wns

Can you please share any sample file ?

Regards
Prince
04-18-2013, 03:26 PM
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
04-18-2013, 04:19 PM
LalitPandey87

Try this and follow the steps:

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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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.
04-18-2013, 04:48 PM
d380013

Hello Lalitji

its working for one stock only if there is much more Stocks then Formula not working pls refer my sheet
04-18-2013, 04:52 PM
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
04-18-2013, 07:06 PM
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.
04-19-2013, 10:00 AM
d380013

Sir ,

i send a detail e-mail for desire output

Thanks

Dhaval Shah

98240 35253
04-19-2013, 12:49 PM
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
04-19-2013, 01:16 PM
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:
04-19-2013, 01:19 PM
d380013

sir i am not getting any update
04-19-2013, 01:24 PM
LalitPandey87

I mean to say my 2nd post on this thread. :rolleyes:
04-19-2013, 01:28 PM
d380013

Yes sir i try it but its not working
04-19-2013, 01:43 PM
d380013

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

pls help me out to get solution
04-19-2013, 02:59 PM
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.
04-19-2013, 07:52 PM
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:
04-19-2013, 08:20 PM
LalitPandey87

1 Attachment(s)

Try this and follow the steps:

Select cell R14:R32, press F2 and paste below formuls and confirm it with CSE

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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

Code:

=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.

Attachment 695