You can write a lookup formula that returns multiple values using tools like VLOOKUP or XLOOKUP, but the FILTER function in Excel is a faster and easier option.
For example, I want to look for a specific company name in a range of data and return any phone numbers that are associated with that name.
The FILTER Function
Let's walk through the steps to write this function.
1. Start by typing the equal sign = and the word FILTER, then hit tab.
2. There are three arguments in this formula. The first is array, which is the range of cells that that we want to return values from. In this case, the phone numbers.
3. Next is the include argument, which is our filter criteria. For our example, we will take the list of companies and set it equal to cell B2.
4. Then, for the [if empty] argument, you can type whatever you want in quotation marks, such as “Not found.”
Finally, with all of our arguments written, we can hit Enter, and you will see that all of the phone numbers associated with the specified company spill into the cells below our formula.
Hope this quick overview of the FILTER function helps you to give it a try in one of your spreadsheets. Leave a comment if you have questions or feedback!