How to return many rows from a single value? It is possible and very easy, but not with the VLOOKUP function.
VLOOKUP isn't the function to use ⛔
The VLOOKUP function was never designed to return multiple rows, nor was the INDEX function even. These functions, although widely used in spreadsheets, can only return a single result (that's how it is).
The only way to return multiple lines from a value is to use the FILTER function .
FILTER is part of the dynamic arrays functions. The FILTER function returns as many rows as there cells for a criteria.
The FILTER function is only available for Microsoft 365 or Excel Online. FILTER is part of the dynamic array functions . These are new functions, introduced in 2019, which allow you to return a result in many cells, and not only one.
Example with tracking number
In the following document, we have the tracking time for different package (tracking number). The tracking number is the key. A tracking number can be made up of several packages.
We want to find all the product references composing a shipment
Step 1: Select the column to return
We will use the FILTER function to return only the column of product references, so the first argument of the function is the B column.
Step 2: Write the condition of the Filter
Now, we indicate the criteria to apply. This is very simple to write.
- Select the column which contained the values to filter
- Indicate the value of the filter
Step 3: Remove the duplicate values
As you can see, the function returns duplicates. You can avoid this by embedding the previous formula in the UNIQUE function.
The combination of the FILTER and UNIQUE functions return exactly the number of values in only 3 steps 😀👍
If I change the tracking number, immediately, the function returns another result (here 4 rows).
Another example with many rows
Now, we want to return 2 informations
- The poste ID (column C)
- The tracking date and time (column D)
The beauty of the function FILTER is that you can use more than one columns to return.