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.
but…but…but…
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.
Conclusion
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.
Can I use vlookup in a more traditional sense, so for instance in a normal table I can use vlookup to convert the results of Month(a1) = 12 into “Dec” by referencing a seperate table that conversions month numbers to a name. I know that I can use Transform / Date / Month name however I want to be able to call my months what ever I want or even not to have labels for some if I don’t want to
Hi and thank you very much! It worked well with the first Merge. But I want to make other merge activities for other columns, like clinic name, Procedure Name, Branch name. When I do the above steps, the data rows grow and show an unrealistic number of patients. for example the CSV raw data is 540,000 rows, when I apply the above I get 900,000.
please guide me what am I doing wrong.
You’re not doing anything wrong. Instead, this idea of merging tables is denormalizing the data. The (usually) desired action is to normalize data (or keep it normalized, as was the initial state of the article example). Normalization is the separation of data into tables so as to reduce the amount of duplicate data. You can find more information about data normalization on the Internet.
That said, what is the solution? Instead of merging tables and creating duplicate data, use Power Pivot or Power BI to establish DATA RELATIONSHIPS after you have reshaped the data using Power Query. If no reshaping of data is required, you can go directly to Power Pivot. Data relationships do a similar thing as the merge instruction described, except the data stays in its separate tables and the relationships established connect the lookup fields across tables, just like a database does. Then, you can display whatever you want from Power Pivot in the form of a table, pivot table, chart, etc.
Power BI adds an extensible set of rich, interactive visualizations and allows greater collaboration and sharing of data across an organization. It also leverages Power Query and Power Pivot, bringing them into a single interface.
Hope this helps.
I expect that this only works as long as there are only unique values once in the lookup sheet. Otherwise the query will duplicate values to match all of then or display all of them which is an issue…also if they are not found in the lookup values, what does the query return?
This was exactly my issue. Thanks for pointing it out, it works perfectly now 🙂
I’m comparing two trial balances and it is obviously only displaying amounts for the accounts that match. However, one of the TB’s has a new account number that the other is missing. How can I have unmatching items displayed?
Thanks
It was quite useful! Thank you.
Thank You
I would have to say power query is better.
This is like =VLOOKUP(,,,0). What about =VLOOKUP(,,,1)? I have a Transactions table and I have a Points table. The number of points I need to attach to the Transaction data is based on a range of how much someone spent. For example,
Transaction Total = $100
Points:
MinSpent | MaxSpent | Points
0 0 0
1 10 1
10.01 20 2
20.01 50 5
50.01 100 10
So, the VLOOKUP(,,,1) value would be 10. The VLOOKUP(,,,0) would return a #N/A error. I need my join to return a 10.
yep; the number of people that don’t seem to understand the default behaviour of vlookup()
Hi,
How can I do this with 3 or more tables? For example a color table with those product ID’s ?
Kind Regards,
Koen
fantastic, thanks
Thnak you puneet
I have 2 columns
ID Certification
1001. C
1002. Java
1003. Python
1004. C#
1005. C++
Now I have other sheet.
ID C. Java. Python C#
1001. Yes
1002. Yes
1003. Yes
1004. Yes
1005.
Every time, I manually filter it and see for certifications. Or through pivot , I have to go to individually.. Is there any way to automate this by formula or by any other sorts of methods to solve this?
What if we have the Final table file on LAN (Shared drive) and files from which data is coming on individual PC which are connected to LAN. Will it be updated?
Yes, I believe.
Very useful information
Thank you Excel Guru
You’re welcome.
Thank you Puneet for always helping out. I really appreciate this
Hi It’s good to know and use VLOOPUP
but i;m struggling to apply proper procedure, can somebody please help me with simple table so that i can understand
Absolutely.
For earlier versions of EXCEL (i/e 2007 and below) I’d also recommend leaving alone VLOOKUP and using INDEX + MATCH which is more flexible and easier on maintenance
I’m with you… But there’s a strong reason why VLOOKUP is so popular.
A very good article. A practical solution is provided where volumes of data and are required to be matched.
I’m glad you liked it.
Supper command, My life is mare easy now with power query. Thanks to excel guru
I’m so glad you liked it.
Power query generates neat and clean data
Also it is real time
Yup 🙂