Dynamic Arrays

How to return many rows with VLOOKUP

Reading Time: 2 minutes

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.

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

List of 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.

=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 which contained the values to filter
  2. Indicate the value of the filter

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

Filter return several rows

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 UNIQUE function returns only unique values

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).

The FILTER function returns 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.

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

FILTER return many rows with 2 columns

Related posts

Depending drop-down list

Frédéric LE GUEN

Dynamic filter in Excel

Frédéric LE GUEN

UNIQUE Function – Remove duplicates with formulas

Frédéric LE GUEN

Leave a Comment