How to Perform VLOOKUP in Power Query in Excel

Last Updated: December 25, 2023

- Written by Puneet

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.

data table to use power query vlookup

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.

power query vlookup column needs to shift

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:

  1. Create queries (connections) for the both of the tables.
  2. Choose the column which is common in both of the tables.
  3. 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.
apply table to data to use power query vlookup
  • Next, you need to load data into the power query editor, and for this, go to Data Tab ➜ Get & Transform Data ➜ From Table.
power query vlookup get 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).
power query vlookup click and load 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.

30 thoughts on “How to Perform VLOOKUP in Power Query in Excel”

  1. 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

  2. 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.

  3. 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?

  4. 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?

  5. 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


    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.

  6. Hi,

    How can I do this with 3 or more tables? For example a color table with those product ID’s ?

    Kind Regards,

  7. 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

    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?

  8. 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?

  9. 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

  10. 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

  11. A very good article. A practical solution is provided where volumes of data and are required to be matched.


Leave a Comment