Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulastextjoin

Vlookup, Hlookup to find and concatenate values in matrix in google sheets

Problem: Sheet 1 should return concatenated values to a certain index that is also hold in sheet 1. Sheet 2 should be searched for this value.

In my case: Sheet 1 holds a string (e.g. "Demos - Sales Gespräche mit Leads") in column B. Now I would like to look for this value in a matrix in Sheet 2 and give back all column headers of the columns that hold this value ("Demos - Sales Gespräche mit Leads"). In my example there would be a couple of names of the person who is assigned to the certain job.

Here is an example sheet: https://docs.google.com/spreadsheets/d/1Q3CrdNbWyVwUAnODwBA7G05FSPLThBHHlWMItt6PNDc/edit?usp=sharing

Possible solution: Some combination of concatenate, vlookup, index and hlookup. I just can't think of how to do this.

Thanks

Solution

  • paste where you need and drag down:

    =ARRAYFORMULA(IFERROR(TEXTJOIN(", ", 1, 
 IF('Sheet 2'!$A$2:$Z=B2, 'Sheet 2'!$A$1:$Z$1, ))))

    0