TEXTJOIN: Multiple Lookup Values in a Single Cell (With/Without Duplicates) Using Only #Excel Formulas by David Hager

A recent post on Twitter pointed to an article on the very good trumpexcel.com site.

https://trumpexcel.com/multiple-lookup-values-single-cell-excel/

In the article, the following statement was made.

“Can we get multiple lookup values for an item in a single cell (separated by comma or space)?

I have been asked this question multiple times by many of my colleagues. And after a lot of head-banging, I realized that it is extremely difficult (if not impossible) to do this using excel functions.”

The problem was solved there with a VBA solution. Well, let’s see if the same scenario can be solved by using only Excel formulas. To be honest, the complete solution I will demonstrate could not be easily accomplished before the introduction of the TEXTJOIN function, which appeared after the article was published.

BTW, the first mention of TEXTJOIN with unique elements can be seen in this video.

https://youtu.be/QJ2O07EB80Q

If the items in the lookup column are unique, the solution is relatively easy. The formula

=TEXTJOIN(“,”,,IF(arange=E3,brange,””)) where arange is in Column A & brange in Column B

can be array-entered in F3 and filled down (see the following figure). So, for regions 1-10, the corresponding lookup matches are shown in the delimited strings.

However, for the case where the lookup column contains duplicates, the task to return unique delimited strings for each region is much more difficult. The simplified form of the array to be used in building the final formula is:

cArray=INDEX(brange,N(IF(1,some_array)))

where:

“some_array”=SMALL(IF(arange=\$E3,ROW(brange)-1,””), ROW(INDIRECT(“1:”&COUNT(IF(arange=\$E3,ROW(brange),””)))))

There are several important parts to the SMALL array (which contains the array positions of the desired items from brange). The main array used as the first argument of the SMALL function is:

IF(arange=\$E3,ROW(brange)-1,””)

In my experience, this kind of formula construction is rare, but it works.

The second argument of the SMALL function is:

ROW(INDIRECT(“1:”&COUNT(IF(arange=\$E3,ROW(brange),””)))))

The really interesting thing about this formula is that it has to be dynamic with respect to each region since they have differing numbers of lookup values.

Two additional defined name formulas are needed for the final formula construction.

countArray=COUNTA(cArray)

=ROW(INDIRECT(“1:”&countArray))

which creates another dynamic array using the same techique as above.

The final formula is:

=TEXTJOIN(“,”,,IF(MATCH(cArray,cArray,0)=RowArray,cArray,””)) You can see in the figure that none of the strings in column F contain duplicate values. As a comparison, an adjacent column using the TEXTJOIN formula from the first example shows the difference in regions 4 and 9. Hope that you find this technique useful. You can download the file from the following link.

MultipleLookupValues

8 thoughts on “TEXTJOIN: Multiple Lookup Values in a Single Cell (With/Without Duplicates) Using Only #Excel Formulas by David Hager”

1. c

Is it possible you can make a formula to:

Search all strings in one delimited cell with multiple lookup values in one delimited cell then return matching values and the unmatched strings concatenated or textjoined into one delimited cell without duplicates

Other considerations:
1. using only Excel Formulas (No UDF or Google Script)
2. search values, lookup values, return values have different delimiters
3. minimal to no helper columns, if possible
4. works both in Google Sheets and Excel 2016 and above with little changes
5. the worksheet exclusively works vertically but it’s better that the formula will not be tied down to horizontal or vertical for other people
6. the worksheet needs no case sensitivity but an equivalent formula with case-sensitive matching might be useful for other people

Please send me an e-mail if you need more explanation or an example.

This is the closest I can find https://www.get-digital-help.com/2010/12/20/excel-udf-lookup-and-return-multiple-values-concatenated-into-one-cell/#searchdel but it is UDF and does not have multiple lookup values.