Power Query XLOOKUP: Matching Data in Excel Explained

Power Query does not have an XLOOKUP function. To match rows from two tables in Power Query, use Merge Queries; use the Excel XLOOKUP worksheet function when you want a formula that returns a matching value in a cell.

What people mean by “Power Query XLOOKUP”

The phrase usually describes a goal rather than a feature: find a key in one dataset and bring back related information from another. For example, a sales table may contain product codes while a product table contains product names and categories. XLOOKUP can return a category for each sales row, and Power Query can combine the tables before loading the result into Excel.

These tools solve related problems in different places. XLOOKUP is an Excel worksheet function. Power Query is Excel’s data import and transformation tool, where matching is done through a join operation. Power Query uses its own language, M, for transformation steps; typing an Excel formula such as XLOOKUP into the Power Query Editor will not make it work there.

How XLOOKUP works in a worksheet

XLOOKUP searches one range for a value and returns the corresponding item from another range. It is convenient when the result should appear in a worksheet cell and update as the workbook recalculates. By default, it looks for an exact match and returns the first matching item it finds.

For a simple example, suppose customer IDs are in A2:A100, customer balances are in C2:C100, and the ID to search for is in E2. If E2 contains C104, A12 contains C104, and C12 contains 275, use this formula:

=XLOOKUP(E2,A2:A100,C2:C100,"Not found")

The formula returns 275. The fourth argument supplies a readable result when the ID is absent; without it, XLOOKUP normally returns the #N/A error for a missing match. The lookup and return ranges must have compatible dimensions, and the formula returns the first match when the lookup range contains repeated IDs.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It is not available in older perpetual versions such as Excel 2019. If the workbook must be used by people on older releases, test compatibility or consider a method they can use, such as INDEX and MATCH.

How a Power Query merge matches tables

A merge joins two queries using one or more columns as matching keys. The result is a new query containing columns from the original data and a nested column representing matching rows from the second query. You can expand that nested column to select the fields you want to add.

For example, merge a Sales query with a Products query on ProductID. Expanding ProductName and Category adds those product details to the sales data. This is the closest Power Query equivalent to looking up fields from a related table, but it operates on whole tables during a query refresh rather than calculating a separate worksheet formula in each row.

A merge does not require the matching column to be the first column in either table. It can also use multiple keys: select the first key column in each preview, then select additional key columns in the same order while holding Ctrl on Windows. Both sides need corresponding columns with compatible data types; a text ID on one side and a numeric ID on the other may fail to match as expected.

Merge two Excel tables in Power Query

On Excel for Microsoft 365 for Windows, first make each source range an Excel table. Select a cell in the range and press Ctrl+T, confirm the range and headers, then use Table Design > Table Name to give each table a clear name. Repeat for the second range. This helps the query use a defined data source rather than an arbitrary block of cells.

Select a cell in the first table and choose Data > From Table/Range. Power Query Editor opens. Confirm the headers and data types, then use Home > Close & Load To if you need to control how the query is loaded; otherwise, create the second query in the same way.
In Power Query Editor, choose Home > Merge Queries > Merge Queries as New to keep the source queries and create a separate combined query. Choose the first and second queries in the merge dialog.
Click the matching key column in the first preview and then the corresponding column in the second. For multiple keys, select the corresponding columns in the same order on both sides. Choose a join kind and select OK.
In the resulting query, use the expand button in the merged column header. Select the fields to bring in, and optionally turn off the option that prefixes expanded column names with the source query name.
Check the output for missing matches, unexpected extra rows, and suitable data types. Choose Home > Close & Load to return the result to Excel.

Menu labels and available connectors can vary by Excel build. Power Query is also available in Microsoft 365 for Mac, but the available sources and authoring options can differ from Windows. If the exact merge controls are not present in a Mac or web build, use the Windows desktop version for these steps; Excel for the web does not provide the same full Power Query authoring experience.

Choose the right join kind

The join kind determines which rows survive the merge. For a lookup-style result where every row in the first, or primary, table must remain, choose Left outer. This is the usual choice when adding product or customer fields to an existing sales table. Rows without a match remain, with null values in the expanded fields.

Left outer: Keeps all rows from the first table and matching rows from the second. Use it for most “bring these details onto my main table” tasks.
Inner: Keeps only rows that have a match on both sides. Use it when unmatched records should be excluded from the result.
Full outer: Keeps matching and unmatched rows from both tables. This can help compare two lists and find records that appear on only one side.
Left anti: Keeps rows from the first table that have no match in the second. This is useful for finding missing product IDs or unregistered customers.
Right anti: Keeps rows from the second table that have no match in the first. Depending on the selected table order, it can reveal lookup-table entries unused by the main data.

Power Query also offers right outer and right anti joins. Rather than choosing a join by name alone, decide which table is primary and whether unmatched rows should stay. The order of the tables in the merge dialog matters for left- and right-based joins.

Duplicates, missing keys, and other common problems

A frequent surprise is that a merge can increase the number of rows. If the lookup table has two rows for the same key, a matching row from the primary table can appear twice after you expand the results. That is different from ordinary XLOOKUP behavior, which returns one result: the first match. Before merging, check whether the second table is meant to have one row per key. If it is, fix or aggregate duplicate keys using an appropriate business rule rather than deleting records blindly.

Missing matches often come from inconsistent keys rather than a faulty merge. Check for leading or trailing spaces, different data types, inconsistent punctuation, and IDs that have lost leading zeros. An ID such as 00127 may be meaningful text; converting it to a number changes it to 127. Set both key columns to the intended type and clean the source values consistently before joining.

Nulls in expanded fields mean the join found no corresponding row, or the matched source field itself is empty. To distinguish those cases, temporarily expand a reliable nonblank key from the second table. For troubleshooting, a left anti join can isolate primary-table rows that have no match. Fuzzy matching is available for certain text merges, but it is not a substitute for clean identifiers: similar-looking names can be matched incorrectly, so review its results carefully.

When to use XLOOKUP, Power Query, or both

Choose XLOOKUP when the source data is already in the workbook, you need a quick cell-level answer, and formula recalculation is the desired behavior. It is also practical when a user should be able to change a lookup key in a cell and see the result immediately, without refreshing a query.

Choose Power Query when you import, clean, combine, or reshape a dataset as a repeatable process. A merge can add several fields in one operation, retain a documented sequence of transformation steps, and refresh the combined result from its sources. It is often easier to manage for recurring reports than copying a lookup formula down thousands of rows, especially when the input files change regularly.

The two methods can work together. For example, use Power Query to import and clean a monthly transaction file, then load the cleaned table to a worksheet and use XLOOKUP for an interactive result elsewhere in the workbook. Alternatively, merge the reference table in Power Query and load a complete report. Choose based on where the matching should happen and how the result needs to be maintained.

Remember that a query result is generally updated by refreshing the query, not simply because a source cell changed. Use Data > Refresh All to refresh workbook queries. A worksheet XLOOKUP recalculates according to Excel’s calculation settings. If a source table gains rows, confirm that the query’s source includes them; using Excel tables as sources usually makes expansion easier to manage.

Frequently Asked Questions

Can I use XLOOKUP inside Power Query?
No. XLOOKUP is an Excel worksheet function, not a Power Query function. Use Merge Queries for a table join, or perform a transformation with Power Query’s M language if a merge is not the right operation.

What is the Power Query equivalent of XLOOKUP?
For matching rows between two tables, it is Merge Queries. A left outer merge most closely resembles adding lookup fields while keeping every row from the primary table. The result can include multiple matches, unlike XLOOKUP’s single returned item.

Why did my Power Query merge create duplicate rows?
The second query probably contains more than one row for at least one selected key. Each matching row can be included when the merged column is expanded. Check key uniqueness and decide how duplicates should be handled before expanding.

Can Power Query return only one match?
A merge returns rows according to the join and the matches present; it does not automatically mean “take the first match” as XLOOKUP does. If only one row per key is appropriate, make the lookup query unique using a clear rule, such as grouping or selecting the correct record based on a date, before merging.

Does Power Query refresh automatically when I change a source cell?
Not in the same way a worksheet formula recalculates. Query output updates when the query refreshes. Use Data > Refresh All, or configure refresh behavior in the query’s connection properties when that option is available in your Excel version.

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.