How to VLOOKUP Multiple Matching Rows Using PowerQuery

How can we VLOOKUP multiple matching rows in Excel?

VLOOKUPS are a great function in Excel but they cannot be used to find multiple matching rows in a Lookup table. VLOOKUPS only return the first matching row that they find and then ignore any other matching rows.

So how can VLOOKUP multiple matching rows in a Lookup table?

We use the merge function in PowerQuery.

Why is Merging Better Than A VLOOKUP?

Merging in PowerQuery in Excel or in Power BI is in some ways like a VLOOKUP. It uses the information in a column of data that’s present in two datasets (called a key dataset), to then take the information from a lookup dataset and put it into a base dataset.

Merging can be better, because you don’t need to write a different VLOOKUP for each different column you want to transfer, merging will do them all at once (if you want them to). However, where there are duplicate key values in the table you are looking up to (the lookup table), a VLOOKUP will only return the first item that it finds and ignore any others. If there is different information in each of the rows with the duplicate key values then they will be ignored, with only the first row in the table being returned. Merging brings in all matched rows from the lookup dataset, but creates duplicate rows in the base table to accomodate all the lookup dataset rows.

In some cases this isn’t useful!! Perhaps if your base dataset has some values in it that you want to sum, creating duplicate rows, will also create duplicate values. If you’re then trying to tie your dataset back to a value like a TB or Accounts Receivable subledger then that’s not going to work!

However in other circumstances it can be extremely useful, such as splitting costs to multiple cost centres by manual journals and that’s the scenario we’ll look at here.

The Video

Play Video
  • 00:44 What's the scenario and where is a PowerQuery merge better than VLOOKUPS? -
  • 04:30 - How to connect your base and lookup datasets to PowerQuery
  • 06:13 - How to merge two queries to create duplicate rows
  • 07:42 - How can we expand merged information to choose the columns that we want to bring into the merged dataset?

How to create duplicate rows using the Merge Function in PowerQuery which VLOOKUPS do not.

In the example video, we want to allocate some credit card bills to different cost centres depending on who’s spent the money. The cost goes to the cost centre of the person who’s spent the money. This is easy enough if each person just belongs to one cost centre. You just need the credit card information (being the base list) and a list of people who have credit cards and their cost centres (the LOOKUP dataset) and VLOOKUP the information from the Lookup table to the base table.

However, if each employee belongs to more than one cost centre then you can’t use the VLOOKUP as the VLOOKUP will only return the first cost centre that it finds. The merge however will bring in any matching rows and create duplicate rows in the base table to accommodate all of the lookup table’s rows. Easy!

Here are the steps you need to take: remember to watch the video for full instructions!

a) Firstly connect both base data and lookup data to PowerQuery in separate queries. In Excel, go to the Data tab then Get Data, then From File and then from Workbook. In PowerBI, go to Transform Data and then Excel. From here you can select the workbook and then the sheet that you want to connect to.

b) Use the Merge Queries as New option to bring the two datasets together. Make sure that you put the Base Data first and the lookup data second when PowerQuery asks you which datasets to bring together. Also don’t forget to highlight the columns in both datasets that join the information together. Leave the join kind as Left Outer as this retains all the information in the base dataset and merges in matching information from the lookup dataset.

c) Expand the Merged Query: once the new query has been created, you’ll see a table that looks like the base dataset except with a new column on the end with the word “Table” in each row. In this column’s header there’s a little button with two arrows that go in opposite directions. This is the expand button. Click on it and then you can choose the columns that you can bring into the base dataset from the lookup dataset.

d) Adjust the cost for each row. Because we’ve created a duplicate row every time PowerQuery has come across a duplicate row in the lookup table we’ve also created duplicate credit card costs. We then need to multiply the credit card costs by percentages to allocate the correct cost to each cost centre. This can be easily done through the Add Column tab and then the Custom Column option. You can multiply the Credit Card cost column by the Payroll Split column using the “*” operator as in Excel

From these steps we’ll then have a merged query that has brought together the credit card information and the payroll information. We can close this query and load into Excel.

 

.... and a little more!

Connecting to a single file – Of course, if you need to post this kind of journal every month, then simply replace the data in the base dataset and click refresh. The video shows a technique where we connect PowerQuery to an Excel file. To do the same journal in another month, save over the base dataset file with the new file but keep the file name and address the file and then click refresh.

Connecting to a  folder – Of course you could also save the base dataset file to a folder and then keep on adding new base datasets to the folder and click refresh every time you get a new file.

Taking it further – We finish the video once we’ve merged the base dataset and lookup dataset together. You could take the query further, creating a journal for posting into an accounting system with account codes, cost centres, entities and so on, Alernatively, you can take the output of the query in the video and then create a journal in Excel from that information.

 

Check out some of our other popular blogs for saving Finance Teams time!