 # Index/Match with multiple results visible cell

• 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,

• Can you please post a sample workbook?

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

• Actually try this...

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

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

• 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?

## Files

• 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)

• 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,"")," "))," ",",")

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