Power Query XLOOKUP From Another Table: How It Works

Power Query does not run the Excel XLOOKUP function inside a query. To return columns from another table, use Merge Queries to match rows by a shared key, then expand the matching column; the result can be refreshed when the source data changes.

Why use a merge instead of XLOOKUP?

XLOOKUP is a worksheet function: it searches a range or array and returns a corresponding value in a cell formula. Power Query is a data import and transformation tool. It works with tables of data before loading the result into a worksheet or data model, so it uses a join operation—not XLOOKUP—to combine tables.

A merge is the closest Power Query equivalent to looking up a value in another table. For example, an Orders table might contain ProductID and Quantity, while a Products table contains ProductID, ProductName, and Category. Merging on ProductID lets you add product details to each matching order row.

This approach is useful when the task is part of a repeatable import or cleanup process. After setting up the query, you can refresh it to incorporate changed source data instead of maintaining formulas in every output row. It is also suited to combining tables that are too large or awkward to manage with worksheet formulas.

How Power Query matches rows

A merge compares one or more columns in a first query with corresponding columns in a second query. The selected columns are the join keys. When the values match, Power Query creates a nested column containing the related rows from the second query. You then expand that column and choose which fields to bring into the result.

The join kind controls which rows are retained. For a common lookup-style task, choose Left outer (all from first, matching from second). This keeps every row from the first table and adds related data where a match exists. Rows without a match remain, with null values in the added fields. An inner join instead keeps only rows that match in both queries.

Unlike a conventional single-result lookup, a merge can return more than one matching row. If the lookup key appears several times in the second table, expanding the merged column can repeat a first-table row for each match. That behavior matters when you expect exactly one result per key.

Prepare the tables before merging

Good source data prevents many merge problems. In Excel for Microsoft 365 on Windows, format each dataset as an Excel table, give the tables clear names, and check the key columns before importing them into Power Query.

Use a stable key. Choose an ID or other value that identifies the record, rather than a name that may be duplicated or edited.
Check data types. A number in one query and text in the other may not match as expected. Set both key columns to compatible types in Power Query.
Check for duplicate keys. The lookup-side table should normally contain one row per key if each original row is meant to receive one result.
Look for inconsistent text. Extra spaces, different spellings, or other formatting differences can prevent a match. Clean or standardize the key values consistently in both queries.
Use clear column names. Descriptive names make it easier to select the correct keys and identify fields when you expand the merge.

To create a query from an Excel table, select a cell in the table and choose Data > From Table/Range. In the dialog, confirm the table range and whether it has headers, then select OK to open Power Query Editor. Repeat for the other table. If the data is already in Power Query as queries, you can merge those directly.

Merge another table step by step

Open the query that should keep its original rows. In the example, this is the Orders query.
In Power Query Editor, select Home > Merge Queries. Choose Merge Queries as New instead if you want to create a separate output query and leave the original query unchanged.
In the merge dialog, select the second query from the lower table list. Click the key column in the first preview, then the corresponding key column in the second preview. For a multi-column key, select the columns in the same order in both previews, using Ctrl-click on Windows.
Choose the join kind. For a lookup-style result that retains every first-table row, select Left outer (all from first, matching from second). Check the previews and the match indication before selecting OK.
In the resulting query, find the new column containing a table icon and an expand button. Select the expand button, tick the fields you need, and select OK. Clear the option to use the original column name as a prefix if you prefer shorter output headings.
Review the rows and column names, then choose Home > Close & Load to return the result to Excel. To choose a destination or load to the Data Model, use the load options available in your Excel build.

If both tables are in the same workbook, they need to be available as queries for this workflow. Creating a query from each Excel table is a straightforward way to make them available. For files or databases, import each source as a query first; the merge steps are essentially the same once both are listed in Power Query Editor.

Example: add a product category to orders

Suppose Orders has OrderID, ProductID, and Quantity. Products has ProductID, ProductName, and Category. Merge Orders with Products on ProductID using a left outer join, then expand ProductName and Category. An order for ProductID P-10 will receive the product details from the Products row with P-10. If P-10 is absent from Products, the order remains in the output and the expanded product fields show null.

For comparison, a worksheet formula can perform a lookup after the data is loaded. With ProductID in Orders[ProductID] and the matching keys and categories in Products[ProductID] and Products[Category], enter this in a new Orders table column:

=XLOOKUP([@ProductID],Products[ProductID],Products[Category],"Not found")

For a row whose ProductID is P-10, this returns the category in the matching Products row; if there is no match, it returns Not found. This formula uses Excel table structured references and assumes the table and column names shown. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It is an Excel formula, not a step to paste into Power Query.

Important differences from a worksheet lookup

A merge is not always a one-for-one replacement for XLOOKUP. XLOOKUP ordinarily returns one result for a lookup value, whereas a merge can associate one row with multiple matching rows. If a product key is repeated in Products, expanding the merge can multiply the corresponding Orders row. Decide whether duplicates are legitimate; if not, correct them or remove duplicates from the lookup query using a rule that preserves the intended record.

Also decide what should happen to unmatched keys. A left outer merge preserves them with nulls, while an inner merge drops them. You can filter for nulls after expanding to investigate missing references, or replace null values if a particular display value is appropriate. Do not replace nulls automatically if the distinction between “no match” and an actual blank value is important.

Power Query results are snapshots of the source at the time of the last refresh, not live cell-by-cell calculations. If a source changes, refresh the query to update the output. A worksheet XLOOKUP recalculates under Excel’s calculation settings when its referenced values change; choose between the two based on whether you need a transformation workflow or a live worksheet calculation.

Refresh, load, and platform notes

After loading the merged result, refresh it with Data > Refresh All or refresh an individual query from its table or query controls. A refresh reruns the query steps against the available source data; it does not mean that the output is continuously synchronized. If a source file moves, a table is renamed, or credentials change, the query may need its source settings or permissions updated.

Excel for Microsoft 365 on Windows is the primary set of steps above. Power Query capabilities and available connectors can vary by Excel version and platform. Excel for Mac has Power Query features, but the interface and supported sources may differ by build; use its Data tab and verify that the needed connector and merge options are available. Excel for the web may not provide the same query-authoring experience as desktop Excel, so create or edit the query in a supported desktop version when the required controls are missing.

Common merge problems and fixes

No rows match: Compare key values in both query previews. Check for incompatible data types, extra spaces, inconsistent IDs, or values that are genuinely absent from the second table.
More rows appear after expanding: The second query likely has duplicate values in the key column. Inspect those duplicates and decide how they should be handled before merging.
Unexpected nulls appear: A left outer merge retains unmatched rows. Confirm that the selected columns are the intended keys and that their values use compatible types and formatting.
The wrong fields are added: Reopen the expand selection and choose the needed columns. Renaming fields in the source query before the merge can also make the output easier to read.
Refresh fails: Check whether the source location, file access, table name, or credentials have changed. The query depends on those source details remaining available.

Frequently Asked Questions

Can I use XLOOKUP in Power Query?
No. XLOOKUP is an Excel worksheet function. In Power Query, use Merge Queries to match a key and expand the related fields.

Which join type is like XLOOKUP?
A left outer join is usually the closest choice when you want to preserve every row in the first table and add matches from the second. It retains unmatched first-table rows with nulls.

Why did my merged table get extra rows?
The second query probably contains multiple rows for at least one selected key. Expanding a merge returns each matching row, so check for duplicate keys if you expected one result per original row.

Can Power Query merge tables in different workbooks?
Yes, if both sources are imported as queries and are accessible. Their locations and permissions must remain valid for refresh to work.

Does a Power Query merge update automatically?
Not as a live formula. The result changes when you refresh the query, manually or through refresh settings that you configure in Excel.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.