How to return multiple rows with VLOOKUP

Last Updated on 13/11/2023
Reading time: 2 minutes

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

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

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 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) 😀👍

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

FILTER return many rows with 2 columns

1 Comment

  1. SM
    07/08/2024 @ 18:41

    Thank you for this post.
    I have a further question - In the first example,
    Example with tracking number - TRK-001

    Is it possible to automate for multiple tracking numbers - example TRK-001 and TRK -004 and additional Tracking numbers?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

How to return multiple rows with VLOOKUP

Reading time: 2 minutes
Last Updated on 13/11/2023

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

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

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 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) 😀👍

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

FILTER return many rows with 2 columns

1 Comment

  1. SM
    07/08/2024 @ 18:41

    Thank you for this post.
    I have a further question - In the first example,
    Example with tracking number - TRK-001

    Is it possible to automate for multiple tracking numbers - example TRK-001 and TRK -004 and additional Tracking numbers?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *