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

%3CLINGO-SUB%20id%3D%22lingo-sub-3070829%22%20slang%3D%22en-US%22%3EMatch%20Cells%20on%20Sheet%201%20%26amp%3B%202%20and%20return%20value%20from%20multiple%20cell%20on%20Sheet%202%20to%20one%20cell%20on%20Sheet%201%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3070829%22%20slang%3D%22en-US%22%3E%3CP%3ESheet%201%2C%20Column%20A%20list%20names.%20Sheet%202%20Column%20A%20lists%20names.%20Some%20names%20are%20listed%20multiple%20times%20but%20with%20different%20values%20in%20Sheet%202%20Column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20name%20in%20Sheet%201%2C%20Column%20A%20matches%20the%20name%2Fnames%20in%20Sheet%202%2C%20Column%20A%2C%20then%20the%20value%2Fvalues%20in%20Sheet%202%2C%20Column%20B%20needs%20to%20be%20added%20to%20Sheet%201%2C%20Column%20B.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20i'm%20currently%20working%20with%26nbsp%3BIFERROR(INDEX(Sheet2!B%3AB%2CMATCH(A6%2CSheet2!A%3AA%2C0))%2C%22nothing%20found%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20the%20issue%20that%20I'm%20having%20is%20that%20once%20it%20finds%20the%20matching%20name%20from%20Sheet%201%20Column%20A%20in%20Sheet%202%20Column%20A%20it%20stops%20searching%20for%20other%20instances%20of%20the%20same%20name%20in%20Sheet%202%20Column%20A%20(these%20multiple%20instances%20have%20different%20values%20in%20their%20Column%20B).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20just%20not%20entirely%20sure%20if%20it's%20possible%20to%20merge%20several%20cells%20from%20Sheet%202%20Column%20B%20into%201%20cell%20from%20Sheet%201%20Column%20B.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sheet%202%22%20style%3D%22width%3A%20333px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342091i42E011769FC46A41%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Sheet%202.PNG%22%20alt%3D%22Sheet%202%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESheet%202%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sheet%201%22%20style%3D%22width%3A%20291px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342092i85AE156880730FBA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Sheet%201.PNG%22%20alt%3D%22Sheet%201%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESheet%201%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3070829%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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)