How to VLOOKUP Multiple Matching Rows Using PowerQuery

How to VLOOKUP Multiple Matching Rows Using Power Query?

VLOOKUPS (and XLOOKUPS!) 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 VLOOKUPs return multiple matching rows from a Lookup table? We abandon VLOOKUPS and instead use Merging in Power Query.

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 column), 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 accommodate 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.

…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, Alternatively, you can take the output of the query in the video and then create a journal in Excel from that information.