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.
- Select the column that contained the values to filter
- 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))
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?