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

Can you please share any sample file ?

Regards
Prince

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

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.

Hello Lalitji

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

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

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.

Sir ,

i send a detail e-mail for desire output

Thanks

Dhaval Shah

98240 35253

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

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: