Wednesday, April 17, 2024
Education STEM

Simple & brilliant way to extract multiple matches in one cell

 

The scenario:

You have a dataset with values in one column that’s unique (or semi-unique) and another column with its associated values as Key:Value pairs but that column has repeating  values. Imagine a list of members whose names are in one column and their corresponding membership status next to it as below:

What we want to do is to extract all the names whose membership are Active and whose are Expired in the table below:

This is a very small dataset, so you can eyeball it, or even be tempted to use Index(), Match(), LookUp(), sort/group or Pivots. But for really large datasets, there’s a much quicker and simpler solution. Besides, the Index or LookUp aren’t going to work as they’d return a single value per cell.

Meet Excel’s TEXTJOIN() function. With some simple brilliance, we can combine it with IF() and alternatively with REPT() function. I prefer the IF() because it seems cleaner and less hacky but I’ll explain both methods.

Here’s the output:

How it works:

Method 1:

For Active members: =TEXTJOIN(“;”,1,IF($D$3=B3:B31,A3:A31,””))

We use a semi-colon to separate the names (we can use any delimiter we need however).

Then we do a conditional nested IF() which basically tests if the value we want to lookup by (in this case, Active, which is in D3), anchor it for absolute reference, and tell it to look for all matches in the given range (Status column/B). If there is a match, we retrieve its corresponding value from Customer column (A). If there’s no match, we do nothing). The second parameter gives us the option to ignore or include blank cells (we set it to TRUE or 1 in this example).

For the Expired members, we simply need to change the lookup value from D3 to D4 and anchor it.

To debug/verify, press F9 while highlight the full IF() function to see what it’s returning shown in the formula bar, and you’ll see the returned values (for Active):

{"";"Olivia";"Chloe";"Sophia";"Emma";"Emily";"Mia";"";"Sarah";"";"";"";"";"Ava";"Joanna";"Amelia";"Evelyn";"";"";"Ariana";"Maya";"";"";"";"";"Fatima";"Anna";"Aria";"Abigail";""}

Method 2:

We can get the same results using the repeat function called REPT(). Now the formula looks something like this (for Active members):

=TEXTJOIN(“; “,1,REPT(A3:A31,(B3:B31=$D$3)*1))

REPT() repeats the extracted values (item name) once (hence *1) if the range of invoice#s contains the invoice# we’re looking for (Lookup Value col). Beware that the order of range parameters are opposite to when using the method 1.

So, there you have it. Countless analysts have spent immeasurable amount of time (hacking with AGGREGATE and INDEX, etc.) creating really ugly formulas to solve this type of challenge for ages…until now. And now, you don’t have to either 🙂

!!! Click here to see Excel books on Amazon !!!

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top