In this blog post we explore how to replicate a VLOOKUP in Power Query using merge queries.
Excel users love VLOOKUP. It is one of the most useful functions there is. So it stands to reason that we may need to perform similar tasks in Power Query.
In this blog post we will look at performing common VLOOKUP scenarios by using merge queries within Power Query. And also discuss some of the differences (advantages of Power Query) between the two.
Advantages of using Power Query over VLOOKUP
Now Power Query may not always be the route I would take. Sometimes I just need quick answers. So I would bash out a quick VLOOKUP to get the results, and paste to values (if needed) to remove the formula.
Power Query has many advantages over the classic VLOOKUP function however. Simplifying tasks that with VLOOKUP would be awkward, or in some cases not possible.
Here are some of its advantages;
- Power Query can work with data from external sources directly (CSV files, databases, web queries) without first dumping the data into a worksheet. This can keep your workbooks lean and mean.
- In Power Query we could easily return multiple columns of data for the lookup value without the need for a separate VLOOKUP for each column.
- We can lookup based on multiple columns/conditions using the Power Query approach without needing complex array formulas or helper columns.
- Other steps could be added to Power Query to apply formatting, or remove bad rows.