VLOOKUP Return Multiple Matching Rows and Columns

In this post, we’ll discuss a way to simulate using VLOOKUP to return multiple matching rows and/or columns. What do you mean by “simulate” Jeff? Well, VLOOKUP is designed to return a single value, not multiple values. That is, VLOOKUP scans down the lookup range and stops at the first matching row … ignoring any additional matching rows. Once VLOOKUP finds a matching row, it shoots to the right to retrieve the related value from a single column. But let’s say we want to return the values from multiple columns and all of the matching rows. Well, this is where VLOOKUP breaks down. So, we’ll simulate this functionality by turning to Power Query. Let’s get to it … I have a video and full written narrative below.

Video

Narrative

Before we get too far, let’s just clarify our objective. Let’s say we have a list of invoices, and our list contains summary information such as InvoiceID and Customer … something like this (Summary table):

We also have an invoice detail list, which contains line-item details for each invoice. It contains the InvoiceID, Item, Amount, and perhaps other info. Something like this (Detail table):

Now, we want to go through each item in the summary table (each invoice) and retrieve related values from the detail table. You’ll notice there are multiple matching rows in the detail table for each invoice. Plus, we want to retrieve multiple columns from the detail table, for example, the Item and Amount columns.

When we are done, we want to combine multiple matching rows and columns like this:

Since VLOOKUP is limited to retrieving a single cell value from the first matching row and the specified return column, we’ll use Power Query instead.

We’ll take it in three steps:

  • Merge query
  • Specify multiple columns
  • Combine multiple rows

So, let’s get to it.

Note: if this were a one-time project, you may be able to use VLOOKUP to retrieve the CustID from the summary table into the detail table if you use approximate match and sort the summary in ascending order by the lookup column. Then write one formula for each column value you want returned. But in practice, Power Query will create a solution that is easily refreshed in future periods as the data tables change. 

Merge query

In this first step, we will load each table into Power Query and then merge them. So, first, we select any cell in the Summary table and select the Data > From Table command. The table is loaded into Power Query:

Then, we click Home > Close & Load To… and select Only Create Connection as shown below:

We do the same steps again to load the Detail table into Power Query.

Now, we need to merge these two tables with Power Query. One way to do this from inside the Power Query editor is to select the Home > Merge Queries command. One way to do this from inside Excel is to select the Data > Get Data > Combine Queries > Merge command. Either way, you’ll be presented with the Merge dialog where you identify the Summary table first and then the Detail table. Then, you identify the lookup column by selecting the InvoiceID column in both tables, as shown below.

Note: the Join Kind is Left Outer because we selected the summary table first and then the detail table. If you selected Detail first and then Summary you’ll want to change the Join Kind to Right Outer. Depending on what you are working on, other Join Kind options may be helpful so check them out. 

Now, we click OK and we find ourselves inside the Power Query editor:

With our queries merged, it is time to perform our next step.

Specify multiple columns

Recall that one thing we want to accomplish is to return multiple column values. VLOOKUP can return a value from a single column, but we can easily return multiple column values with Power Query.

To do so, just click the Expand icon on the right side of the Detail column header, or the Transform > Structured Column > Expand command. You’ll be able to pick one or multiple columns to return from the detail table:

Here, we want to return the Item and Amount columns. We click OK and bam … we have returned multiple columns:

Now that we have returned multiple columns, let’s address the rows.

Specify multiple rows

At the end of the day, we want one row for each InvoiceID, so, we select the InvoiceID column and then the Transform > Group By command. Since we want to display a variety of columns for each InvoiceID, we select Advanced in the resulting Group By dialog:

Now, we define each additional column desired. In our case, we would like 3 columns:

  1. New column named Amount, that computes the Sum, of the existing Amount column.
  2. New column named Count, that will Count Rows.
  3. New column named Items, that includes All Rows

The updated dialog is here:

We click OK and:

And, we are almost there! The final step is to convert the Items column into a comma-separated list of the actual item values found in the detail table.

To do this, we’ll use a trick that Ken and Miguel taught me in their workshop (by the way, check out their amazing M is for Data Monkey book).

We click Add Column > Custom Column. In the resulting Custom Column dialog, we enter a desired column name, like ItemList, and write the formula shown below.

Note: the [Items] argument references the table name and the “Item” references the column name.

Clicking OK results in a new ItemList column, like this:

Now, we need to tell Power Query that we want to convert each list in the ItemList column into a comma-separated list of values. To do this, we select the Expand button on the right side of the ItemList header and select Extract Values… . In the resulting Extract values from list dialog, we pick our preferred delimiter. Here, we’ll go with a Comma:

Click OK and bam!

We can remove the Items column, and then click Home > Close & Load To … and send it to a Table. The results appear in Excel like this:

And, that is exactly what we set out to do!

Now, it took quite a few steps to get here, but now for the best news. Once this is set up, all we need to do in future periods is right-click the results table and select Refresh. New rows in the summary or detail tables are automatically included. This means that the investment of time to set this up will pay efficiency dividends every subsequent period.

If you have any other Power Query tips or ways to improve this process, please share by posting a comment below … thanks!

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

19 Comments

  1. Flo on November 14, 2019 at 8:41 am

    Brilliant.
    I actually had a need to do this last week. And wrote a stupidly long formula with IFs, ANDs and NOTs in it to get the required results. Sadly my version of Excel is so old that this brilliant solution wouldn’t have worked for me anyway – but I’m saving this away for the day that we upgrade and I need to do it again.
    Thank you.

  2. DJ on November 16, 2019 at 11:38 am

    WOW! Thank you so much Jeff! Will definitely have use for this.

  3. Tatiana Petkova on November 18, 2019 at 9:10 am

    It’s really great! Thank you!

  4. Christina on December 3, 2019 at 8:45 pm

    That’s awesome! Thank you for the clear explanation!!

    I have a question. If I want to have separate excel tab or even excel sheets by the Invoice ID and have it list those details, is there way I can formulate so that excel sheet identified by an Invoice ID lists all the pertaining columns?

    For example: an excel sheet A1 shows 1001. Starting on A3, I would want it to start listing the items and the corresponding amounts that are on invoice 1001.

    Any ideas/feedbacks is much appreciated! Thank you!

  5. Judge_Nutmeg on January 9, 2020 at 11:20 am

    WOW! That was amazing.

    I am running excel 2013 but downloaded the Power Query Add on and followed your excellent tutorial.

    This will make my job easier now, cant thank you enough 🙂

    • Jeff Lenning on January 9, 2020 at 11:28 am

      Welcome 🙂

  6. Will on January 15, 2020 at 4:50 pm

    This just saved me. Thank you! Thank you!

    • Jeff Lenning on January 15, 2020 at 4:50 pm

      Glad it helped!!
      Thanks
      Jeff

  7. Arun on March 8, 2020 at 3:38 am

    Powerful tool. Thanks . Helped to solve long standing problem with ease..Thank you

  8. Shital on July 10, 2020 at 1:24 pm

    Thank you so much. Explained so wonderfully.

  9. Grayson on August 14, 2020 at 10:51 am

    I have an issue where I want to be able to type in a certain value (i.e. A1-1) and it return a list of items that are associated with this particular value. For example, if I type in A1-1, I need it to return 8 different rows that make up A1-1, including the name, quantity, dollar value and extended dollar value.

    While this is one example for A1-1, there are many other items that I need to be able to bring over a list, like A1-A, B1-1, etc… Any recommendations?

  10. Drew on September 5, 2020 at 4:05 pm

    Thank you for your amazing tutorial on Power Query, I really think this can help me with a computational problem I had with a huge amount of data. Just had one doubt. In the last step where you made Power Query return all items as part of one cell separated by commas, I want all items to be in separate columns. Moreover, in my case the number of items is different for each Invoice ID so some columns for items will be empty. How do you think I should go about this one?

  11. Shruti on September 14, 2020 at 12:29 am

    I get below error while creating itemlist.
    Expression Error: We cannot apply field access to the type Text.

  12. Brian Lim on October 4, 2021 at 3:49 am

    I like this function, but i keep get error msg Expression.Error: The field ‘LogNo’ of the record wasn’t found.

  13. Brandon on October 14, 2021 at 7:00 am

    This excellent and concise blog has made me the talk of the office.

    Thanks for making my life easier!

  14. Marcy on October 29, 2021 at 3:07 pm

    After I select Add Column > Custom Column, and enter Table.Column([Items[,”item”) then press ENTER, the “ItemList” column displays with “Error” for each record. What am I doing wrong?

  15. Joshua Godec on December 7, 2022 at 1:51 pm

    This is super cool.. thank you for putting this together.

  16. Andy on August 9, 2023 at 1:59 pm

    I know this was done a while ago but when i duplicate your exercize, i get “table.column not recognized…”

    Dont understand…

Leave a Comment