Match Cells on Sheet 1 & 2 and return value from multiple cell on Sheet 2 to one cell on Sheet 1

Copper Contributor

Sheet 1, Column A list names. Sheet 2 Column A lists names. Some names are listed multiple times but with different values in Sheet 2 Column B.

 

If the name in Sheet 1, Column A matches the name/names in Sheet 2, Column A, then the value/values in Sheet 2, Column B needs to be added to Sheet 1, Column B. 

 

This is the formula i'm currently working with IFERROR(INDEX(Sheet2!B:B,MATCH(A6,Sheet2!A:A,0)),"nothing found")

 

However, the issue that I'm having is that once it finds the matching name from Sheet 1 Column A in Sheet 2 Column A it stops searching for other instances of the same name in Sheet 2 Column A (these multiple instances have different values in their Column B).

 

I'm just not entirely sure if it's possible to merge several cells from Sheet 2 Column B into 1 cell from Sheet 1 Column B. 

 

Thank you in advance for your help!

 Sheet 2Sheet 2Sheet 1Sheet 1

1 Reply

@divinetofu90 

=IFERROR(INDEX($B$1:$B$12,SMALL(IF($E1=$A$1:$A$12,ROW($1:$12)),COLUMN(A1))),"")

 

Maybe with this formula as in the attached file. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021. Formula in cell J1 in the attached example is: =CONCATENATE(F1,G1,H1,I1)