VLOOKUP is one of the most popular Excel functions and there’s no doubt about it. You know this from the very beginning of your Excel journey. But today I have a something new for you, an 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 return the values from the same row of the different column or from the same column.”
Let’s Start with Data
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 product ID column before the category column in TABLE 2.
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.
- 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 power query editor, and for this, go to Data Tab ➜ Get & Transform Data ➜ From Table.
- After that, close the query from Home tab ➜ Close and load to ➜ Connection only. (Repeat Step 2 and 3 for the second table).
- Now from the 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 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 category column.
- In the end, click “Close & Load” to load table into the worksheet.
BOOM! now you have a new table with category column.
In power query, all you have 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 check out this Excel Power Query Tutorial.
You must Read these Next
- Unpivot Data with Power Query: Using power query for unpivoting a data table is a real fun. You will be surprised...
- Consolidate Data from Multiple Worksheets: One of the best Excel options which I have learned about managing data...
- Pivot Table from Multiple Worksheets: Excel doesn't allow you to refer to different worksheets. But sometimes, it happens...
- VLOOKUP MATCH: In VLOOKUP, col_index_no is a static value which is the reason VLOOKUP doesn’t work like a...
- Two Way Lookup: Performing a two-way lookup is all about getting a value from a two-dimensional table. That means...
- IFERROR with VLOOKUP: In this combination, both of the functions work in a sequence. VLOOKUP works in the...
- Wildcards with VLOOKUP: A normal VLOOKUP doesn’t allow you to lookup for a value like this, but when you...
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.