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.

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.

Pingback: #Excel Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You