Multiple search results

It is common knowledge that the VLOOKUP function cannot return results that are “to the left” of a given range, and this problem is solved by XLOOKUP, which additionally allows you to search the range from the beginning or from the end. However, what if these functions need to return more results? To do this, you used to need to use array formulas, but today this is performed in a much easier way…

In this example, we’ll use the data table called Food as the range to be searched, which consists of two columns that contain the name of a country and the name of a famous dish from that country.

We will use the names of the countries as a criterion. We can extract them from the first column using the formula:

=SORT(UNIQUE(Food[COUNTRY]))

How to return multiple search results for a given country? Let us write the formula:

=IF(Food[COUNTRY]=D2, Food[DISH],””)

Excel returns a dynamic table that represents the values that match the search criterion. If we wanted to return the results by columns, we could nest this formula inside the TRIMRANGE function, but it may be better to return the results as a comma-separated list. In this case, we will nest the formula inside the TEXTJOIN function.

=TEXTJOIN(“, “, TRUE, IF(Food[COUNTRY]=D2, Food[DISH],””))

Now all you have to do is copy the formula to the rest of the cells. As a result, we will get multiple search results. All dishes that belong to a particular country, the name of which we use as a search criterion.