Since VLOOKUP is one of the most popular Excel functions it makes sense that one of the first things you want to do in Power Query is VLOOKUP. But step away from the Add Custom Column button because there’s not a formula in sight.
Excel Power Query VLOOKUP is actually done by merging tables. Makes sense if you think about it, after all a VLOOKUP is simply pulling a column from one table into another table.
This tutorial applies to Excel 2010 onwards and requires the Power Query add-in, or if you have Excel 2016 you'll find it on the Data tab in the Get & Transform group.
The Data
Our data is in a simple table called “data” (nothing like stating the obvious), containing a date, product name and sales amount:
We have a lot of different products and I’d like to group them into categories so they’re easier to analyse.
In comes table number 2, called “categories”, which maps my products into their respective categories:
An aside, if you were to use VLOOKUP formulas to bring the Category name into the "data" table you’d have to either switch the column order in the "categories" table so Product was in column A and the Category in column B, or use INDEX & MATCH, or some other clever manipulation of VLOOKUP to make it lookup to the left.
Not with Power Query, it's not fussy about column order, as you'll see.
Excel Power Query VLOOKUP
- Format your two tables as an Excel Table (CTRL+T and make sure they have headers)
- Load the data table into Power Query: Excel 2010/2013 Power Query tab > From Table, or Excel 2016 Data tab: Get & Transform group > From Table
- Close the Query: Home tab > Close and load to > Connection only
- Repeat steps 1 through 3 for the categories table
- In the Workbook Queries pane in Excel right-click the data query > Merge:
- In the Merge dialog box select the categories table in the bottom section:
- Left click your mouse on the Product column in the data table. It should turn green.
- Left click your mouse on the Product column in the categories table. It should also turn green. This tells Power Query which columns to match up.
- The Join Kind will default to ‘Left Outer’ which is fine since I want to make sure all of the records in my first table (data) are retained even if there isn’t a corresponding category for a product.
Click OK, which will open the Power Query editor window:
You can see we have the first table (data) in the first 3 columns and then a NewColumn which is effectively our second (categories) table.
- Click on the double headed arrow on the NewColumn. This displays a list of columns in the categories table. Uncheck the Product column since we already have this in our data table. Also uncheck the ‘Use original column name as prefix’ and click OK.
- Now we have a new table which includes the product category column:
- I’ll do some tidying up:
- Drag the Category column in between the Date and Product columns
- Format the Date column as Data Type: Date:
- Format the Sales column as Data Type: Currency
- Close and Load To a Table:
And now you have a new table in an Excel worksheet containing your 4 columns ready for analysing in a PivotTable or formulas.
I know that seems like a lot of steps but it actually only takes around 1 minute to set it up when you know what you're doing, as you can see in this video (no sound):
And the best thing is if your source data changes (new data, new categories, new anything), you can update the query by clicking the Refresh button on the data tab.
Download the Workbook
Power Query VLOOKUP Approximate Match
Replicating a VLOOKUP Exact Match formula in Power Query is easy, as you've seen above, however replicating a VLOOKUP Approximate Match formula in Power Query isn't, but that's a post for another day.
More Power Query
The Definitive Guide to Power Query; what it's good for, what versions of Excel can get it, where to download it and more.
Please Share
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
David says
I have Match replicating VLOOKUP Exact Match, but can’t figure out a way to have it replicate Approximate Match. Any thoughts?
Catalin Bombea says
Hi David,
use: “*” & LookupValue & “*”, the asterisc wildcard will allow for partial matches, can be any text before or after the lookup value. If you want to allow only prefixes or suffixes, remove the corresponding wildcard.
Catalin
Duncan Williamson says
Good tutorial Mynda.
As an aside, I worked through the example and it works well! No surprise! No but … !
For no real reason I left the two tables as two tables and added them to the data model. I then created a relationship between the two tables using Product in both cases. I know you know this but it works exactly the same as the merged table solution you are presenting here.
FYI
the merged tables solution is 161Kb on my W10 Excel 2016 system
the relationship solution is 227 Kb on the same system
Duncan
Mynda Treacy says
Hi Duncan,
Yep, that’ll work if you have one table with a column of unique values and if that table contains all instances of the product. Might not always be the case.
Power Pivot is good at compressing large amounts of data, less so with small tables like in this example.
Cheers,
Mynda
Alex says
I liked this. Thank you. A power query for you: How can I do this same thing but base the “lookup” off the first few characters. For example, I would like to merge but on all values that START WITH “AMZ”. Is this possible? Thanks.
Mynda Treacy says
Hi Alex,
Power Query merging doesn’t allow for partial matches. You’d need to split the AMZ etc. into a different column so you can match the entire column.
Mynda
Power Query Newbie says
Hi. I’m new to Power Query but I can see that it would make my job so much easier. I am facing a dilemma though, mainly on making sure that each column has the right data within it. Isn’t Power Query supposed to recognize a column based on a column name, not on its order in the worksheet? For example, I’ve got a file with names, ages and gender (in that order with columns named accordingly) and another file with names, grades, ages and nationalities (also in that order with columns named accordingly). When I append or merge the files or load from a folder, the table shows the data from the two files in the same order that the columns are arranged. Consequently, column 2 will have a mixture of ages and grades, and so on.
How can this be avoided without having to go into each file to fix the column order?
Thanks.
Mynda Treacy says
Power Query doesn’t match column names when you append or get data using ‘From Folder’. It’s up to you to ensure your data structure is identical for these techniques.
Alternatively, you can get each file in a separate query and then merge the queries. When you merge you can specify which columns match so you don’t end up with a jumble of data.
Mynda
Stephen says
Thanks for this Mynda.
I’ve been looking at using PowerQuery and PowerPivot for processing my bank and credit card statements, rather than my existing formula-based approach. I’m very impressed with these tools and want to work with them more – if I can work out how to use them in my real-life scenarios. They just seem a powerful and sophisticated approach, and I prefer using built-in tools than extensive personal customisation – and I view extensive macros and formulae as effectively customisation that comes with the ‘cost’ of having to continually update them as things change.
One of the major issues for me arises with categorisation. Consider the ‘Categories’ table in the example. Every single item (product) has to have a row here that matches the product name exactly, whereas a formula-based or macro-based approach can check for substrings (e.g. a company name) within the item string (product name) and assign the appropriate category.
Do you have any ideas how categories can be assigned on a similar basis in a PowerQuery/Pivot approach?
Thanks in advance
Mynda Treacy says
Hi Stephen,
Great to hear you’re interested in Power Query and Power Pivot. They are amazing tools.
Power Pivot won’t allow you to create relationships between tables unless there is an exact match, so to speak.
Power Query has functions which can find substrings. e.g. Text.Contains Text.StatsWith Text.EndsWith so you may be able to utilise them in your model. You’d have to write some custom functions to look through a list of ‘categories’ to see if they are within the text string, so it’d be some pretty advanced M code.
Mynda
Stephen says
Thanks for the tips Mynda.
These powerful tools seem to have been rather quietly slipped into Excel as far as most Office users are concerned, and I’m only just beginning to explore them – which is why I’m following your posts.
I am honestly amazed how much database-like functionality Microsoft has been able to introduce into Excel. MS Access showed us what a mess the average user makes of a true database product, but tools like Power Pivot make such powerful functionality usable by average Excel users.
I was unaware of these Power Query functions, so I’ll take a good look. Perhaps I had better spend some time reading a few tutorials to get a balanced understanding of Power Query and Power Pivot capabilities, rather than trying to pick them up by trial and error.
As to the macros, hopefully I will be to start from those I wrote to do multiple searches from such “a list of ‘categories'” when the number of searches meant that a single formula was getting ridiculously long.
Thanks again, and keep up the good work
Mynda Treacy says
Yes, Excel has come a long way now that we have the Power tools.
I don’t advise trying to pick them up by trial and error. Power Pivot in particular needs to be set up the right way from the outset, or you’ll find yourself rebuilding it from scratch if you get it wrong.
I can recommend some courses ;-p
https://www.myonlinetraininghub.com/excel-power-query-course
https://www.myonlinetraininghub.com/power-pivot-course
Mynda
Mark says
Hi Mynda,
Is there any chance you could add a link to download the source data file so we can follow your instructions and check we get the same answer?
Thanks
Mynda Treacy says
Hi Mark,
You can download the workbook here.
Mynda
Glenda says
This is awesome information. Thank you so much for sharing this. I know I’ll be using this one in the future.
Mynda Treacy says
Thanks, Glenda. Glad you’ll find it useful.
Mynda
Paranam Kid says
Step 12 tidying up, you state:
“Format the Date column as Data Type: Date:”
But in the green table below the numbers in that column still don’t have $ or € or …. signs in front of them.
Mynda Treacy says
Hi Paranam,
The ‘formatting’ in Power Query is different to that in Excel. In Power Query it is setting a data type. This helps Excel know whether the data is text, numbers etc., but more importantly it helps Power Pivot know what type of data it is as well.
I cover this in more detail in my Power Query and Power Pivot courses.
In Excel the formatting you have available is a cosmetic appearance on the cell, it doesn’t affect the underlying value contained within that cell. It also offers you the ability to use other characters in the format, like currency symbols etc.
Kind regards,
Mynda