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).

mlv1

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,””))

mlv2

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.

mlv3

Hope that you find this technique useful. You can download the file from the following link.

MultipleLookupValues

Advertisements

One thought on “Multiple Lookup Values in a Single Cell (With/Without Duplicates) Using Only #Excel Formulas by David Hager

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s