In Excel, a Power Query lookup is usually a merge: open the Power Query Editor, choose Home > Merge Queries, select the two tables and their matching key columns, then choose a join type. For a typical lookup that adds fields from a reference table while keeping every row in the main table, use a left outer join and expand the columns you need.
For example, you can match an Orders table to a Products table using ProductID, then add each product’s name and category to the orders. Unlike a worksheet formula copied down a column, a Power Query merge becomes part of a repeatable data-preparation process: when the source data changes, refresh the query to rebuild the result.
What a Power Query lookup does
Power Query is Excel’s data import and transformation tool. It can connect to tables, files, databases and other sources, apply steps to clean or reshape their contents, and load the result to a worksheet or data model. A lookup is not a separate Power Query function with that name. It is commonly created by joining two queries on one or more matching columns.
The merge produces a column containing matching rows from the second query. That column initially holds nested table values rather than ordinary fields. You choose which fields to expand—for instance, ProductName and Category—so they appear as columns beside the original data.
This is useful when the same reference information is needed repeatedly, when the source is too large or varied for a convenient worksheet formula, or when you want data-cleaning steps to run the same way after each refresh. It is also useful for combining files or tables that have different structures, provided they share a dependable key.
How to merge two tables in Excel
The following steps describe Excel for Microsoft 365 on Windows. Start with two Excel tables or queries, such as Orders and Products. If your data is just a cell range, select it and choose Insert > Table, making sure the header option is correct. Give each table a clear name on the Table Design tab; clear names make queries easier to identify.
- Load each table into Power Query. Select a cell in the table and choose Data > From Table/Range. In the Power Query Editor, review the headers and data types, then use Home > Close & Load or Home > Close & Load To. Repeat for the other table if it is not already a query.
- Open the query that should remain the main result, such as Orders. In the editor, choose Home > Merge Queries. Choose Merge Queries as New instead if you want a separate result query and do not want to change the existing Orders query.
- In the merge dialog, select Orders as the first table and Products as the second. Click the matching column in each preview, such as ProductID. If the key uses multiple columns, select the columns in the same order in both previews.
- Choose the join kind. For a lookup that preserves all Orders rows, select Left Outer. Confirm the dialog, then use the expand icon in the new column header to choose fields such as ProductName and Category. Clear the option to use the original column name as a prefix if you prefer shorter headers.
- Check the resulting rows and column names, then choose Home > Close & Load to return the result to Excel. To control the destination, choose Home > Close & Load To and select the available load options.
On Excel for Mac, Power Query support and the available connectors or editing features can vary by Excel version and update channel. If the described merge controls are unavailable, check that Excel is up to date and use Excel for Windows for the full walkthrough. Excel for the web is not a full substitute for desktop Power Query authoring.
Choosing the right join type
The join kind controls which records survive the merge; it does not merely control which columns are added. A useful way to choose is to ask which table’s rows you need to keep and whether unmatched records matter.
- Left Outer: keeps every row from the first table and adds matches from the second. This is the usual lookup choice. An order whose ProductID is absent from Products remains in the result, with no product details filled in.
- Inner: keeps only rows with a match in both tables. Use it when unmatched records should be excluded, but be aware that it can hide missing keys if you expected every main-table row to match.
- Full Outer: keeps rows from both tables, matching those with equal keys and retaining unmatched rows from either side. This can help reconcile two lists.
- Right Outer: keeps every row from the second table and matches rows from the first. It is often possible to reverse the table order and use Left Outer instead.
- Left Anti or Right Anti: returns rows from one side that have no match on the other. Anti joins are particularly handy for finding missing product IDs or records that need investigation.
For a straightforward lookup, start with Left Outer, then inspect how many rows remain and whether the expanded fields contain unexpected blanks. A blank detail can mean the key was not found, but it can also mean the matching source row itself has a blank value.
A concrete Orders and Products example
Suppose Orders has OrderID, ProductID and Quantity. Products has ProductID, ProductName and UnitPrice. Merge Orders to Products on ProductID using Left Outer, then expand ProductName and UnitPrice. If the order table contains ProductID P-104 and the product table has one row for P-104, the merged order gains that product’s name and price while retaining its original order fields.
Now suppose P-999 appears in Orders but not Products. A left outer merge keeps that order, but the expanded product fields are null, which Power Query displays as blank in the loaded worksheet. This is often preferable to silently dropping the order: it preserves the transaction and makes the missing reference visible for correction.
The example assumes ProductID identifies one product row. If Products contains two rows for P-104, the merge can produce two matches for that order. Expanding the result may therefore create two output rows for one order. That is not a random lookup result; it reflects a one-to-many relationship in the source data. Decide whether duplicates are valid, or clean the product table so the key is unique before merging.
Prepare reliable matching keys
Most failed lookups are key-quality problems rather than a failure of the merge feature. Before joining, confirm that the columns represent the same kind of identifier and that values have consistent formatting. Power Query assigns data types, and a number and a text value that look similar on screen may not behave as the same key. Set the two key columns to compatible types in the editor.
For text keys, check for leading or trailing spaces, inconsistent capitalization, punctuation, and nonprinting characters. In the Power Query Editor, select the text column and use Transform > Format to apply operations such as Trim or Clean; choose a consistent case if capitalization differs between sources. Do not remove meaningful punctuation or zeros from identifiers just to make values look alike. For instance, the text key 0042 may be a different code from the number 42.
Composite keys are useful when no single column uniquely identifies a record. You can select multiple columns in the merge dialog, such as Country and AccountNumber, in the same order on each side. Before doing so, verify that this combination really defines a match. If one side uses different components or conventions, standardize them first rather than relying on accidental similarities.
Duplicates, missing matches and fuzzy matching
Check key uniqueness in the lookup table whenever you expect one result per main-table row. A quick way to investigate is to group or remove duplicates in a separate query, or sort and filter the source to inspect repeated IDs. Removing duplicates is appropriate only when duplicates are genuinely redundant; if they represent different effective dates, locations or categories, the right solution may require an additional key or a rule for selecting the correct record.
When expected matches are missing, compare a few unmatched values directly in both queries. Look for different data types, spaces, spelling, extra characters, inconsistent zeros and blank keys. You can also use a Left Anti join from the main table to the reference table to create a focused list of unmatched records. Fix the source or add a documented transformation, then refresh and check that the unmatched list changes as expected.
For text fields with approximate spelling, the merge dialog offers a fuzzy matching option in supported Power Query experiences. Fuzzy matching can help with names that differ slightly, but it is not a safe replacement for a stable identifier: similar names may refer to different people or products. Review the matches, adjust the available fuzzy options when appropriate, and treat uncertain matches as candidates for validation rather than unquestioned facts.
Refresh, loading and ongoing maintenance
A merge is a saved sequence of query steps. After the source tables or connected files change, use Data > Refresh All in Excel to update the workbook’s queries. Refreshing reruns the transformations against the current source; it does not necessarily fix bad source values, and it cannot create a match when the key is absent.
Keep the source tables and query names stable where possible. If a source column is renamed or removed, a later step that expects the old name may fail. If you add rows to an Excel table, those rows are generally included when the query reads that table; a fixed cell range that is not an Excel table may not expand in the same way. Check refresh behavior after changing a workbook layout or connection.
Queries can load a result to a worksheet or, where available, to the workbook Data Model. A worksheet is convenient for inspecting and sharing a modest result. A data model can be more appropriate for larger analytical models and relationships, but it changes how users explore the output. Avoid loading redundant intermediate queries to sheets unless you need them for review; keeping them as connection-only queries can make a workbook easier to navigate.
Power Query merge versus XLOOKUP
Use XLOOKUP when you need a formula-based answer in a worksheet and the lookup table is already arranged in cells. It is convenient for a small, direct match and recalculates as worksheet values change. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in some older Excel releases.
Choose a Power Query merge when matching is part of a repeatable import or cleanup process, when multiple source tables need transformation, or when you want to refresh a prepared result rather than maintain formulas down a column. A merge can also retain unmatched rows or reveal unmatched keys through join choices. It is not automatically better: a simple formula may be easier for a small live worksheet, while a query can be easier to audit and repeat for regular data preparation.
Power Query also has a different update rhythm. Editing a source cell does not necessarily recalculate the loaded query result immediately; refresh the query. That distinction matters when users expect the output to update instantly after each manual edit.
Frequently Asked Questions
Is Power Query lookup the same as Merge Queries?
Usually, yes. People use lookup informally to describe matching rows from a reference table. In Power Query, the standard way to do that is to merge queries on matching columns and expand fields from the second table.
Which join should I use for a lookup?
Use Left Outer when you want to keep every row in the main table and add fields where a match exists. Use Inner only when unmatched main-table rows should be removed.
Why did a merge create more rows than the original table?
The second table probably has multiple rows for at least one key. Each matching row can appear in the expanded result. Check whether the lookup key is unique or whether the merge needs another key column.
Why are some expanded values blank?
The key may be missing from the second table, the matching row may have a blank field, or the keys may differ in type or formatting. Compare the source values and use an anti join to isolate unmatched records.
Does a Power Query lookup update automatically?
It updates when the query is refreshed, for example with Data > Refresh All. A query result does not behave exactly like a worksheet formula that recalculates immediately after each cell edit.