Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Lookup All Corresponding Values Match Criteria In Adjacent Column Array Formula

  1. #1
    Junior Member
    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0

    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

  2. #2
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0
    Can you please share any sample file ?

    Regards
    Prince

  3. #3
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    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

  4. #4
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    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.
    Last edited by LalitPandey87; 04-19-2013 at 01:15 PM.

  5. #5
    Junior Member
    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0
    Hello Lalitji

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

  6. #6
    Junior Member
    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0
    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

  7. #7
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    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.

  8. #8
    Junior Member
    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0
    Sir ,

    i send a detail e-mail for desire output

    Thanks

    Dhaval Shah

    98240 35253

  9. #9
    Junior Member
    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0
    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

  10. #10
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    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.
    Last edited by LalitPandey87; 04-19-2013 at 01:18 PM.

Similar Threads

  1. Replies: 6
    Last Post: 05-22-2013, 02:23 AM
  2. Replies: 4
    Last Post: 04-24-2013, 10:04 AM
  3. Replies: 3
    Last Post: 03-12-2013, 12:54 PM
  4. Replies: 3
    Last Post: 08-05-2012, 09:16 PM
  5. Replies: 3
    Last Post: 04-08-2012, 09:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •