How to Perform VLOOKUP in Power Query in Excel

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.

but…but…but…

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

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

Conclusion

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.

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

Content Protection by DMCA.com
2018-11-16T06:13:20+00:00

6 Comments

  1. Robert 11 Aug, 18 at 4:56 am - Reply

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

    • Puneet 20 Aug, 18 at 8:10 am - Reply

      I’m glad you liked it.

  2. BN Sridhara 1 Aug, 18 at 3:21 am - Reply

    Supper command, My life is mare easy now with power query. Thanks to excel guru

    • Puneet 1 Aug, 18 at 5:54 am - Reply

      I’m so glad you liked it.

  3. Ajay 31 Jul, 18 at 6:09 pm - Reply

    Power query generates neat and clean data
    Also it is real time

    • Puneet 1 Aug, 18 at 2:47 am - Reply

      Yup 🙂

Leave A Comment