Excel XLOOKUP and Power Query work together, but they do different jobs: Power Query imports, cleans, and combines data, while XLOOKUP retrieves a matching value in the worksheet. A common workflow is to merge data in Power Query, load the result to an Excel table, then use XLOOKUP in another table or report to find values from that refreshed data.
What “XLOOKUP with Power Query” means
XLOOKUP is an Excel worksheet function. Power Query is Excel’s tool for connecting to data sources and transforming data before loading it into a worksheet or the Data Model. They are separate features, not two parts of one formula engine: Power Query does not run an XLOOKUP formula while it transforms a query. Instead, you can use the results of a query as the lookup table for an XLOOKUP formula.
There are two useful ways to combine them:
- Merge in Power Query, then use XLOOKUP: Power Query joins two datasets and loads the combined result. XLOOKUP can then retrieve a field from that result for a particular row in a worksheet.
- Use Power Query’s merge instead of XLOOKUP: If the goal is to add a column from one dataset to another as part of a repeatable data-preparation process, a Power Query merge may do the whole job.
So “integration” usually means a workflow between a query result and a worksheet formula. It does not mean typing XLOOKUP into the Power Query editor.
How the two tools work together
Imagine an orders table with a ProductID and quantity, plus a separate product list containing ProductID, product name, and category. Power Query can import both tables, standardize their columns, and merge them using ProductID. After loading the result, XLOOKUP can use ProductID to return a category, name, or other field in a report.
The distinction is about where the matching takes place. A Power Query merge happens during a query refresh and becomes part of the output data. An XLOOKUP formula runs in the worksheet calculation system and responds to workbook recalculation. If query data changes at its source, refresh the query before expecting formulas that refer to its output to show the new values.
Power Query transformations are recorded as steps, so the same cleanup and merge can be repeated on later refreshes. XLOOKUP is convenient for worksheet-level questions and reports, but it is not a substitute for a documented, repeatable import-and-clean process when the source data itself needs preparation.
When to merge in Power Query and when to use XLOOKUP
Choose based on the task, not on the idea that one tool is always faster or better. A merge is often a good choice when the relationship between datasets is part of the data-preparation process. XLOOKUP is often a good choice when a workbook user needs to retrieve a value in a formula, or when the lookup is a small, visible part of a report.
- Prefer a Power Query merge when you want to append fields to a dataset as it is imported, repeat the same join on each refresh, or apply cleanup steps before matching.
- Prefer XLOOKUP when you need a worksheet result that updates as users change a key, want a clear formula beside the data, or need a quick lookup against an already loaded table.
- Use both when Power Query prepares a dependable lookup table and worksheet formulas retrieve selected values from it for calculations or presentation.
A query merge can also produce multiple matching rows when the lookup key is not unique. XLOOKUP, by contrast, normally returns the first matching result it encounters. Check whether the key is unique before choosing; the tools can produce different outcomes when duplicate keys exist.
Step-by-step: merge data, then use XLOOKUP
For Microsoft Excel for Microsoft 365 on Windows, start with source data formatted as Excel tables. Give each table a clear name, such as Orders and Products, and use consistent data types for the key columns. For example, ProductID should not be stored as a number in one table and as text in the other.
- Select a cell in the first source table and choose Data > From Table/Range. In the Power Query editor, review the detected column types and apply any needed cleanup. Repeat for the other table.
- In the Power Query editor, select the query to which you want to add fields. Choose Home > Merge Queries (or Merge Queries as New if you want a separate result query).
- In the Merge dialog, select the second query and click the matching key column in each preview. Confirm the join kind. For example, a Left Outer join keeps every row from the first table and adds matching rows from the second.
- Select OK. In the merged column, use the expand button to choose the fields to include, such as ProductName and Category. Review the result for nulls or unexpected row counts.
- Choose Home > Close & Load to load the query result to the workbook. If you need to choose a destination, use Home > Close & Load To and select the appropriate option.
- Use XLOOKUP in a worksheet table or report to retrieve a field from the loaded result. Keep the lookup key and return column aligned by using table column names.
The query result table is the bridge between Power Query and XLOOKUP. If Power Query loads the result as a connection only, it will not create a worksheet table for a formula to reference directly. You can instead load a suitable query to a table, or use the query merge itself to produce the needed fields.
Example formula and result
Assume the Power Query result is loaded as an Excel table named Products, with columns ProductID and Category. Assume the table containing orders is named Orders and has a ProductID column. In a Category column within Orders, this formula looks up the current row’s product identifier:
=XLOOKUP([@ProductID],Products[ProductID],Products[Category],"Not found")
For example, if the current Orders row has ProductID P-104, and the Products table has P-104 in its ProductID column with Office Supplies in its Category column, the formula returns Office Supplies. If the identifier is absent, it returns Not found. XLOOKUP uses exact matching by default, which is usually appropriate for IDs.
Structured references make the formula easier to maintain than fixed cell ranges: Excel refers to table columns by name, and table formulas generally fill down as new rows are added. Use a meaningful table name and avoid renaming columns without checking formulas that refer to them. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; earlier perpetual versions of Excel do not include it.
Refreshing data and keeping formulas reliable
Refreshing a query updates its output from the source according to the query’s steps. In Excel for Windows, use Data > Refresh All to refresh workbook connections and queries, or refresh a selected query from the Data tab. After a refresh, worksheet formulas that reference the loaded table can calculate against its current contents.
Refresh is not the same as recalculation, and neither necessarily happens at the moment a source file changes. If results look stale, check that the query was refreshed successfully, that the source path is still valid, and that Excel calculation is not set to Manual. Also confirm that the formula points to the intended query output table rather than an older copy.
For a robust workbook, give query output tables and columns stable names, document where the source data comes from, and avoid manually editing cells inside a query-loaded table. A later refresh can replace its contents. Put user-entered adjustments in a separate table and combine them deliberately rather than relying on edits that may be overwritten.
Common problems and how to diagnose them
- “Not found” despite a visible match: Check leading or trailing spaces, nonprinting characters, and data types. A numeric 104 and text “104” may not compare as the same key. Clean or convert values consistently in Power Query or in the source.
- More rows than expected after a merge: The second table may contain multiple rows for a key. A merge can return multiple matches and expand the first table’s rows. Inspect duplicates and decide whether to remove them, aggregate them, or use a more specific key.
- XLOOKUP returns the wrong duplicate: XLOOKUP returns the first matching item in its search direction by default. If duplicates are meaningful, define which record should win or prepare a unique lookup table rather than assuming the first row is the correct one.
- Formula shows an error after renaming a table or column: Verify the table and column names in the formula. A structured reference depends on the actual names in the workbook.
- Query changes do not appear in the formula result: Refresh the query, check for refresh errors, and confirm that the formula references the query’s current output. If the result table has no matching key, the formula’s not-found value is expected.
Power Query’s merge matching should not be confused with fuzzy matching. A standard merge matches key values according to the chosen columns and data; an optional fuzzy merge is designed to match similar text values and can produce uncertain matches. For IDs, exact, consistently formatted keys are generally safer than fuzzy matching.
Performance, data quality, and design choices
There is no universal performance rule that says a merge is always faster than XLOOKUP, or the reverse. The outcome depends on data size, number of formulas, query operations, source, and workbook design. Avoid copying formulas across unnecessarily large ranges, and avoid loading columns you do not need. In Power Query, remove irrelevant rows and columns early when practical, and use a stable key rather than matching on a display name that can change.
Think about the shape of the output as well as speed. A query merge is useful if the combined dataset is itself the deliverable. XLOOKUP is useful if the workbook should retain separate tables and calculate a value only where it is needed. Keeping the source tables separate can make their roles clearer; a merged table can make downstream analysis easier. Test with duplicate, missing, and malformed keys before relying on a report.
Power Query and XLOOKUP are included features of supported Excel editions, rather than separate add-ins you need to purchase for this workflow. Availability of particular data connectors and Power Query capabilities can vary by Excel edition, platform, and update channel. If a menu item or connector is missing, check the Excel version and supported data source before redesigning the workbook.
Windows, Mac, and Excel for the web
The steps above are written for Excel for Microsoft 365 on Windows. Excel for Mac also includes Power Query capabilities in current Microsoft 365 versions, but the available connectors and interface can differ from Windows and may depend on the version. Look for Power Query commands under the Data tab and confirm that the connector you need is supported on your installation. The Power Query editor’s merge operation is conceptually the same.
XLOOKUP is also available in Microsoft 365 for Mac. On Mac, the common shortcut to recalculate formulas is Control+Option+F9; on Windows, Ctrl+Alt+F9 forces a full calculation. In ordinary use, Excel recalculates formulas automatically when needed. Excel for the web can handle many workbook formulas, but query editing and refresh options depend on the query’s source and the web app’s current support. For a workbook that relies on a particular connector, verify that refresh works in the intended environment.
Frequently Asked Questions
Can I use XLOOKUP inside Power Query?
No. XLOOKUP is an Excel worksheet function, while Power Query uses its own transformation language and editor. You can merge data in Power Query, load the result to Excel, and then use XLOOKUP against that result.
Should I use a Power Query merge or XLOOKUP?
Use a merge when joining tables is part of repeatable data preparation and you want the added columns in the query output. Use XLOOKUP when a worksheet formula should retrieve a value from a loaded lookup table. For some workbooks, using both is sensible.
Does XLOOKUP update when I refresh Power Query?
It can recalculate using the refreshed query output if its references point to the loaded table. Refresh the query first and check for refresh errors; a formula cannot find a new value that has not been loaded into its lookup range.
Why does a Power Query merge create duplicate rows?
The matching key may occur more than once in the second table. Expanding a merge with multiple matches can create multiple output rows. Check key uniqueness and decide how duplicates should be handled before loading the result.
Can Excel 2019 use XLOOKUP with Power Query?
Excel 2019 includes Power Query features, but it does not include the XLOOKUP function. Use a compatible alternative such as INDEX and MATCH, or use a newer Excel version that supports XLOOKUP. Verify the exact capabilities of your edition and update status.
Related
- Excel Sheet Protection: How to Protect and Unlock Cells
- Excel Remove Duplicates: Safe Steps and Alternatives
- Excel Very Slow or Not Responding? Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- LEN SUBSTITUTE Excel Formula: Count Characters or TextTo count how many times a character appears in a cell, subtract the length of the text after…
- Export a Filter List in Excel: Steps and OptionsTo export filtered rows in Excel, apply your filters, select the results, copy the visible cells to a…
- Excel Text Filter Shortcuts: Search and Filter FasterIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off, then press Alt+Down…
- Excel SUM Formula Returns 0? Causes and FixesIf an Excel SUM formula returns 0, first check whether its referenced cells contain real numbers and whether…
- Excel Formula for Exact Match: XLOOKUP and MATCHWith the value to find in E2, lookup values in A2:A6, and results in B2:B6, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found",0)…
- Excel Scenario Manager Tutorial: Compare What-If PlansTo use Excel Scenario Manager, build a worksheet model, then select Data > What-If Analysis > Scenario Manager…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.