Posts by Guyver99

    Note: When you want to add a followup, please add a new reply instead of ammending your previous reply... there won't be any guarantee I will come back to the question otherwise.


    I'll keep this in mind next time for sure, was too excited with your VBA and formula. Thanks again for your time with this, muchly appreciated.


    Ahh yesss I see the extra space mistake, my bad... and your new formula works great now with the "," spacing. Thank you kindly for your VBA script and formula, was pulling my hair out trying to figure it on my own based on samples and google. Next round is on me! Thx again for your time!


    Lastly, If I want to add another lookup value, is it possible with your formula? ie from where you have *(L21=C2:C15) and if (L20=G2:G15)

    Can you please post a sample workbook?


    Hi there, please see attached example. I'll test out your VBA module in the mean time, thank you for taking your time helping out with this.


    Hi again, I've tested your VBA and Array Formulas, at first it looks good! but for some reason I'm getting the following results based on the Sample sheet I sent.


    Filtering date 11-30-17 "Adam" is getting all 3 results. "RedOrangeBrown", however when I filter date 11-29-17 "Cathy" is only getting 2 results "OrangeBrown"


    Also, how to do add a comma in between the results to separate them?

    Hello,



    I have the following index/match lookup only in visible cell working properly, however....


    =IFERROR(INDEX($F$2:$F$500,SMALL(IF(((SUBTOTAL(3,OFFSET($G$2:$G$500,ROW($G$2:$G$500)-MIN(ROW($G$2:$G$500)),,1))>0)*($K$517=$G$2:$G$500)),ROW($G$2:$G$500)-MIN(ROW($G$2:$G$500))+1),1)),"")


    Column F is what I want returning, Column G has the criteria value in them. K517 is the lookup value.


    Now from Column F there are more then 1 results to be return. I want to use the "&" and string all the return results in a single cell. How should I approach?


    To clarify, Say I want to look up "Adam" (K517) it returned the first value "red" from Column F. However, there are also "yellow and Blue" that matches "Adam". So I want in a cell to display (Red, yellow, blue) based on the filtered cell. Hope that make sense, thank you in advance.


    James,


    Hello,


    I'm having similar issues and was able to take advantage of your reply. I just need to go 1 step further.


    I have the following index/match lookup only in visible cell working properly, however....


    =IFERROR(INDEX($F$2:$F$500,SMALL(IF(((SUBTOTAL(3,OFFSET($G$2:$G$500,ROW($G$2:$G$500)-MIN(ROW($G$2:$G$500)),,1))>0)*($K$517=$G$2:$G$500)),ROW($G$2:$G$500)-MIN(ROW($G$2:$G$500))+1),1)),"")


    Column F is what I want returning, Column G has the criteria value in them. K517 is the lookup value.


    Now from Column F there are more then 1 results to be return. I want to use the "&" and string all the return results in a single cell. How should I approach?


    To clarify, Say I want to look up "Adam" (K517) it returned the first value "red" from Column F. However, there are also "yellow and Blue" that matches "Adam". So I want in a cell to display (Red, yellow, blue) based on the filtered cell. Hope that make sense, thank you in advance.


    James,