Power Query Replaces XLOOKUP: Excel Data Merging Made Simple

Bottom Line: Learn how Power Query can be used to merge Excel tables.

Skill Level: Beginner

Historically, VLOOKUP and its successor XLOOKUP have been the go-to functions for Excel users to retrieve data based on a key.

However, these functions come with limitations, such as one-dimensional searches and potential errors with non-exact matches. So, what is the alternative? Enter Power Query!

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Power Query vs Traditional Lookup Formulas

Power Query, a robust tool integrated into Excel, offers a transformative approach to data manipulation.

Instead of relying on conventional lookup functions, Power Query provides a more dynamic and versatile way to merge tables, transform data, and perform intricate tasks with ease.

Leveraging Power Query for Efficient Data Retrieval

Setting the Stage: Preparing Your Data

Before diving into Power Query, make sure your data is structured correctly. Consistency in column formats, clear headers, and distinct identifiers are crucial for accurate results.

A Step-by-Step Guide to Power Query's Merge Feature

1. Accessing Power Query: Navigate to the ‘Data' tab on the Excel ribbon and select ‘From Table or Range'.

A screenshot of the From Table/Range button in Microsoft Excel's Power Query

2. Configuring the Merge: Choose the primary table and column (e.g., ‘rep ID') you wish to match. Subsequently, select the secondary table and its corresponding identifier column.

3. Finalizing the Merge: Adjust any additional settings as required and confirm the merge operation.

Expanding and Finalizing the Data

Once the merge operation is complete, use the ‘Expand' option to select columns from the secondary table.

This action ensures only relevant data is incorporated into your primary dataset. The merged data is now ready for analysis or further processing.

Enhancing Your Excel Experience with Shortcuts

Keyboard Shortcuts

Shortcuts are not just about speeding up tasks; they also enhance precision and reduce the risk of errors. Familiarizing yourself with keyboard shortcuts can save considerable time and effort, transforming your Excel experience.

Check out our 15 Power Query Shortcuts guide here.

The Art of Efficiency

Practice makes perfect. Dedicate time to learn and internalize essential Excel shortcuts. From simple navigation commands to complex data manipulation actions, each shortcut contributes to a seamless and efficient workflow.

Full Tutorial and Resources

If you're excited about Power Query and want to learn more, we have a full tutorial on 5 ways Power Query can replace other Excel functions and help automate routine tasks.


By embracing the capabilities of tools like Power Query and harnessing the efficiency of Excel shortcuts, you can elevate your proficiency and achieve remarkable results.

Will you be using Power Query instead of XLOOKUP? Let us know in the comments below.


Your email address will not be published. Required fields are marked *

  • Both of these methods are fantastic solutions, but it depends on how much you are trying to merge.

    If there is only one column or so to merge, XLookup is awesome. But, PQ is better for merging multiple columns from a table, especially if it is the entire table. As well, if you want to keep the original table in place, without a new merged query table, I argue that XLookup is simpler and better in that case.

  • I love them both but they serve different purposes.

    Power Qiery does merges.
    Xlookup finds a {one) related record

  • I agree with the masses (“2” is plural, right?): use the proper tool for the job.

    For lookups that the function/s was/were designed for, even a column of 20,000, the functions are the proper tool. Using PQ would be like getting out your boar spear and elephant gun to hunt down the pork chops in your fridge. NOT that overpowering something bothers me (Tim the Toolman Taylor devotee here) but the required interactivity vs. “set it and forget it” or the shoehorning the results into a Table not fully part of the operation, just using a column or two to hold results out of maybe 10-20 total columns and then having to refresh again and again… that’s not suitable to a simple lookup, even 20,000+ of them at a go.

    On the other hand, rather than try to find a part of a spoon that will turn this screw or pound in that nail, for actual merging work, say, PQ is the proper tool and the lookup functions are nightmares (often).

    Proper tool for the job. Even an adjustable wrench is not the right tool if you have available the right size wrench. Excel is absolutely like that and PQ is a great thing, but not a panacea.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter