Index/Match with multiple results visible cell

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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,

    Actually try this...


    First, you'll need to add this VBA module (Alt+F11, Insert>>Module)



    Then apply this Array* formula to your spreadsheet.


    =SUBSTITUTE(TRIM(aconcat(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)),$F$2:$F$500," ")))," ",",")


    [arf]*[/arf]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Quote from NBVC

    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?

    Try this formula (after adding the VBA module):


    =SUBSTITUTE(TRIM(aconcat(IF(((SUBTOTAL(3,OFFSET($C$2:$C$15,ROW($C$2:$C$15)-MIN(ROW($C$2:$C$15)),,1))>0)*(L21=$C$2:$C$15)),$D$2:$D$15,"")," "))," ",",")


    confirmed with CTRL+SHIFT+ENTER.


    Modify ranges as required.


    Also make sure that you don't have trailing spaces in column C..... one of the Cathy names had an extra space.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    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)

    Quote from Guyver99

    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)


    Try:


    =SUBSTITUTE(TRIM(aconcat(IF(((SUBTOTAL(3,OFFSET($C$2:$C$15,ROW($C$2:$C$15)-MIN(ROW($C$2:$C$15)),,1))>0)*(L21=$C$2:$C$15)*(L20=$G$2:$G$15)),$D$2:$D$15,"")," "))," ",",")



    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.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Quote from NBVC

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login