How to return all the rows related to the same 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 array functions. The FILTER function returns as many rows as there are cells for a criterion.

FILTER function

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 packages (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.

=FILTER(B2:B48

Step 2: Write the condition of the Filter

Now, we indicate the criteria to apply. This is very simple to write.

1. Select the column that contained the values to filter
2. Indicate the value of the filter

=FILTER(B2:B48;$A$2:$A$48=G2)

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 returns 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 information

• The post ID (column C)
• The tracking date and time (column D)

The beauty of the function FILTER is that you can use more than one column to return.

=UNIQUE(FILTER($C$2:$D$48;$A$2:$A$48=G2))