VLOOKUP is one of the most popular functions in Excel and there’s no doubt about it. You know this from the very beginning of your Excel journey. But today I have something new for you, and you need to learn this right now.
Here’s the deal:
You can use POWER QUERY to match two-column and get values (By using Merge Option). Yes, you heard it right, you can do VLOOKUP in Power Query.
As you know: “VLOOKUP matches values from a column and then returns the values from the same row of the different column or from the same column.”
Let’s Start with Data
If you look at the below data (CLICK HERE TO DOWNLOAD) where we have two different tables with product price and category.
The one thing which is common in these two tables is product IDs. And here I want to have categories in the quantity table.
Why Power Query Instead of VLOOKUP?
As you can see we have product IDs as a common thing in both of the tables.
If you want to use VLOOKUP you need to shift the product ID column before the category column in TABLE 2.
Otherwise, you can use INDEX MATCH. But here we are going to do this with Power Query.
Steps to Perform VLOOKUP with Power Query
Using Power Query to replace VLOOKUP is not just easy but fast and the best part is it’s a one-time setup.
It goes something like this:
- Create queries (connections) for the both of the tables.
- Choose the column which is common in both of the tables.
- Merge them and get the column you want.
But let’s do it step by step and make sure to download this sample file from here to follow along.
- First of all, convert both of the tables (TABLE 1 and TABLE 2) into Excel tables by using Control + T or Insert ➜ Tables ➜ Table.
- Next, you need to load data into the power query editor, and for this, go to Data Tab ➜ Get & Transform Data ➜ From Table.
- After that, close the query from the Home tab ➜ Close and load to ➜ Connection only. (Repeat Step 2 and 3 for the second table).
- Now from here, right-click on the query and click “Merge” or if you are using Office 365 like me, it’s there on the Data Tab ➜ Get Data ➜ Combine Queries ➜ Merge.
- Once you click on “Merge”, it shows you the merge window.
- From this window, select the “Quantity Table” in the upper section and in the “Category Table” in the down section.
- After that, select the column which is common in both of the tables (here product ID is common).
- At this point, you have a new table in the power query editor.
- From here, click on the filter button of the last column of the table and only select category (un-select Product ID) and click OK.
- Here you have a new table with a category column.
- In the end, click “Close & Load” to load table into the worksheet.
BOOM! now you have a new table with a category column.
In a power query, all you have to do is to create the connection for tables and merge the queries.
And the best part is, once you add new data to the quantity list new table will get updated instantly. I hope you have found this power query tip useful, but now, tell me one thing.
Which looks better to you? Power Query or VLOOKUP?
Please share your views with me in the comment section.
I’d love to hear from you, and please, don’t forget to share this post with your friends, I am sure they will appreciate it and if you want to know how to use power query in general, make sure to check out this Excel Power Query Tutorial.