Concatenate multiple results from an index match

excel excel-formula vlookup string-concatenation textjoin
Djones picture Djones · Apr 11, 2017 · Viewed 9k times · Source

I'm trying to concat multiple results from an index match into one cell. Here is an example data set I'm working with:

enter image description here

My desired results:

enter image description here

As you can see, I'm trying to find all customers where the data is 4/12/2017. I then want to combine them into one string in another cell. I've figured out how to sum up the quantity if the date is the same, but just can't figure out how to concatenate the customer.

Answer

user4039065 picture user4039065 · Apr 11, 2017

Use the newer TEXTJOIN function as an array formula with CSE.

=textjoin("|", true, if(a2:a10=date(2017, 4, 12), c2:c10, text(,)))

If your Excel version does not support TEXTJOIN, search this site for [excel][textjoin] for alternatives. Example: TEXTJOIN for xl2010/xl2013 with criteria

enter image description here

Related questions

Excel - find cell with same value in another worksheet and enter the value to the left of it

I have a report that is generated in Excel which contains an employee's number, but not his/her name. Not every employee will be on this worksheet on any given day. In a 2nd worksheet I have a list of …

Read More
Check if an excel cell exists on another worksheet in a column - and return the contents of a different column

What I want to do is to say if the contents of cell D3 (on current worksheet) exist in column A in the first worksheet (in my case entitled list). (and they always do exist somewhere). Return the contents of …

Read More
Combining COUNT IF AND VLOOK UP EXCEL

I have multiple spreadsheets in a workbook and I would like the following in basic English talk: IF worksheet1(cell)A3, appears in 'worksheet2' column B - count how many times it appears in column b 'worksheet 2' So …

Read More