How to Perform VLOOKUP in Power Query in Excel


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

data table to use power query vlookup


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.

power query vlookup column needs to shift

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

  1. Unpivot Data with Power Query: Using power query for unpivoting a data table is a real fun. You will be surprised...
  2. Consolidate Data from Multiple Worksheets: One of the best Excel options which I have learned about managing data...
  3. Pivot Table from Multiple Worksheets: Excel doesn't allow you to refer to different worksheets. But sometimes, it happens...
  4. VLOOKUP MATCH: In VLOOKUP, col_index_no is a static value which is the reason VLOOKUP doesn’t work like a...
  5. Two Way Lookup: Performing a two-way lookup is all about getting a value from a two-dimensional table. That means...
  6. IFERROR with VLOOKUP: In this combination, both of the functions work in a sequence. VLOOKUP works in the...
  7. Wildcards with VLOOKUP: A normal VLOOKUP doesn’t allow you to lookup for a value like this, but when you...

About the Author

puneet one point one

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 find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.


21 thoughts

Leave a Comment

Your email address will not be published.

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

  2. Hi,

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

    Kind Regards,

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

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

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

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

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