How to include all matching values in different rows using xlookup

Hi Miave​,

(if I understood you correctly)

Use this formula:

=FILTER(B2:B13, D2:D13 = 123)

If you want the result to be on a single row, use this formula:

=ARRAYTOTEXT(FILTER(B2:B13, D2:D13 = 123))

In this case, email addresses are separated by commas.

Change the 123 in the formula if necessary.

(the data in cells D2:D13 is given as numbers)

If you are looking for something else, then explain your problem better and provide some details so we can understand how you want to receive this data.

Hope this helps.

IlirU

Hi Miave​ 

I am using "grouped name" as lookup value... so that =xlookup("123", "fx-Grouped Name", "Email") would pull all the emails not just the first one

#1 Couple of points re. formula =XLOOKUP("123", "fx-Grouped Name", "Email"):

• The lookup_value is 123, not fx-Grouped Name
• fx-Grouped Name is the lookup_array
• Email is the return_array

#2 XLOOKUP, like any other matching function (ex. XMATCH, MATCH, VLOOKUP...), always returns the 1st matching "item" - if one exists - only. So what you expect to achieve can't be achieved with XLOOKUP. The FILTER function is probably what you're after but see next point

#3 Given that you expect >= 1 Email address(es) to be returned by your search/match formula, how do you want them to be "grouped" when there's > 1 match? Something like: 'EmailAddress1, EmailAddress44...'?