Excel Power Query Lookup in Another Table: How to Merge

How-to basics · Updated November 21, 2026

To look up values from another table in Excel Power Query, merge the two tables on a shared key: select the first query, choose Home > Combine > Merge Queries, select the second query and matching columns, then expand the merged column to bring in the fields you need. For a typical lookup that keeps every row from the first table, choose a Left Outer join.

What a Power Query lookup does

Power Query does not use a worksheet lookup formula when it matches tables. Instead, its Merge operation joins rows from two queries by comparing one or more columns. The result initially contains a nested column holding the matching rows from the other table. You expand that column to add selected lookup fields to the result.

For example, an Orders table might contain OrderID, ProductID, and Quantity, while a Products table contains ProductID, ProductName, and UnitPrice. Merging on ProductID lets you add ProductName or UnitPrice to each matching order row. The shared column is called the key; it should identify the same real-world item in both tables.

A merge is useful when you want a repeatable data-preparation step. After setting up the query, you can refresh it to apply the same matching process to updated source tables. That is different from copying lookup results and pasting them as values, which does not automatically update when the source changes.

Prepare the source tables first

Organize each source as a proper Excel table before building the merge. A table has a header row, one field per column, and no blank rows or merged cells within its data. Click a cell in the range and press Ctrl+T on Windows, confirm that the table has headers, and select OK. On Mac, use Command+T to create a table, or choose the table command from the Insert tab.

Give each table a clear name on the Table Design tab, such as Orders and Products. Descriptive names make it easier to identify the correct query and table later. Avoid using two different names for the same key field unless there is a good reason; matching column names make the relationship easier to understand, though the names do not have to be identical for a merge.

Check the key values in both tables before joining:

  • Confirm that the columns represent the same kind of identifier. A product code should be matched to a product code, not to a description that happens to look similar.
  • Make sure their data types agree. A number stored as text may not match a numeric value, even if both display as 1042.
  • Look for leading or trailing spaces, inconsistent punctuation, and different spellings. Text matching can be affected by these differences.
  • Check whether the lookup table has one row per key. Duplicate keys can create multiple matches and increase the number of rows after expansion.

To create queries from Excel tables, click in a table and choose Data > From Table/Range. Power Query Editor opens with that table as a query. Repeat for the other table, or use an existing query if the lookup data is already loaded into Power Query.

Merge the queries step by step

  1. In Excel, open the query for the table whose rows you want to keep as the main result. In the Power Query Editor, choose Home > Combine > Merge Queries. Choose Merge Queries as New instead if you want a separate query and prefer to leave the original query unchanged.
  2. In the Merge window, select the second query from the lower table list. Click the key column in the upper preview and then the corresponding key column in the lower preview. The selected column is highlighted and numbered.
  3. If the match uses multiple fields, select each field in the same order in both previews. For example, a transaction might be identified by both StoreID and ProductID. Use Ctrl-click on Windows to select multiple columns. The match requires the selected values as a combination.
  4. Choose the join kind. For a standard lookup that retains all rows from the main table, select Left Outer (all from first, matching from second). The other join choices are explained below.
  5. Select OK. A new column appears with a table icon and a value in each row indicating matching nested data. Use the expand button in the column header to select which fields from the second query to add.
  6. Uncheck fields you do not need. You can usually clear the option to use the original column name as a prefix if you want shorter output headers. Then select OK and inspect the results.
  7. When the output is ready, choose Home > Close & Load, or use Close & Load To to choose where the query result should go. The exact load options depend on the workbook and Excel version.

If the two tables are already queries, you do not need to import them again. Start with the main query in Power Query Editor and merge it with the lookup query there. In Excel for Microsoft 365 on Windows, these instructions describe the standard Power Query Editor workflow. Power Query is also available in Microsoft 365 for Mac, but connectors and editing features can differ by build; use the Data tab’s Get Data (Power Query) commands where available and check that your version supports the needed operation.

Choose the right join kind

The join kind decides which rows survive the merge. It is not just a technical setting: it determines whether unmatched records are kept or excluded. For a lookup that adds information without removing main-table records, Left Outer is normally the right choice.

  • Left Outer: keeps every row from the first, or main, query and adds matches from the second. Rows without a match remain, with null values in expanded lookup fields.
  • Inner: keeps only rows with a match in both queries. Use it when unmatched main records should be excluded from the output.
  • Right Outer: keeps every row from the second query and matching rows from the first. It is less common for a lookup because the first query is usually treated as the main list.
  • Full Outer: keeps rows from both queries, including unmatched rows from either side. It can help reconcile two lists, but the resulting columns may contain nulls where one side has no match.
  • Anti joins: Left Anti returns rows from the first query with no match in the second; Right Anti returns rows from the second with no match in the first. These are useful for finding missing codes or records.

For the Orders and Products example, a Left Outer merge keeps all orders, even if a ProductID is missing from Products. An Inner merge would silently remove those unmatched orders from the result, which may not be appropriate if the goal is to audit incomplete data.

Worked example: add product names to orders

Suppose Orders contains OrderID, ProductID, and Quantity, and Products contains ProductID, ProductName, and UnitPrice. First, load each Excel table into Power Query. In the Orders query, merge with Products by selecting ProductID in both previews and choosing Left Outer. Expand ProductName and UnitPrice, but leave out any other product fields that the report does not need.

The output has the order columns plus the selected product details. If ProductID P-17 appears once in Products, every order row with P-17 receives that product’s name and price. If it does not appear there, the order remains because of the Left Outer join, while ProductName and UnitPrice are null. That null is useful evidence: it tells you the query could not find a matching lookup row.

Now suppose Products contains two rows with ProductID P-17, perhaps because the source has duplicate records. The merge can find both rows. After expansion, an order with P-17 may appear twice, once for each match. This is not Power Query randomly duplicating data; it reflects a one-to-many match in the lookup table. Decide which product record is authoritative or remove duplicates according to a defensible rule before relying on the output.

Fix common matching problems

A merge that returns nulls for records you expect to match is often caused by inconsistent values or types. In Power Query Editor, inspect the small data-type icon in each key column and set both columns to compatible types. For example, convert both product identifiers to Text if leading zeros matter. If an identifier such as 00127 is converted to a number, it may become 127 and no longer match the text key.

Text values can also differ invisibly. A space after a code, a nonbreaking space copied from a website, or a hyphen in only one table can prevent a match. Select the key column and use Transform > Format > Trim to remove ordinary leading and trailing spaces. If capitalization is inconsistent, use Transform > Format > lowercase or uppercase on both key columns so their values are normalized consistently. Review the transformed values before loading the result.

For multiple-column merges, verify that the selected columns are in the same order on both sides. Selecting StoreID and then ProductID in one query, but ProductID and then StoreID in the other, does not define the intended pair of keys. Also check that the fields together uniquely identify the intended record. A single key may be insufficient if the same product code is reused by different suppliers or regions.

Fuzzy matching is available for some text-based merges and can match values that are similar rather than identical. It may help with inconsistent names, but it is not a substitute for a reliable identifier: similar names can refer to different entities, and fuzzy rules can produce surprising matches. Use it only when approximate matching is appropriate, and review the output rather than assuming every suggested match is correct.

Refresh behavior and query maintenance

Once a query is loaded, refreshing reruns its connection and transformation steps. Use Data > Refresh All to refresh workbook queries, or right-click a query result and select Refresh where that command is available. If a source is an Excel table, adding rows within that table generally lets the query pick up the expanded table when refreshed. Adding data outside the table does not automatically make it part of the source.

Power Query records steps such as selecting a join kind, expanding columns, and changing data types. To review or change them, open the query and look at the Applied Steps pane. If a source column is renamed or removed, a later step may fail because it refers to the old name. Keep source headers stable where possible and remove unnecessary steps carefully.

The output is a query result, not a place to enter permanent manual corrections. Changes typed directly into the loaded result can be overwritten at refresh. Correct the source data or add a deliberate transformation step in Power Query instead. If someone needs to review unmatched rows, create a separate Left Anti merge query or filter the merged result for nulls in the expanded key field.

Power Query merge versus XLOOKUP

Use XLOOKUP when you need a formula in a worksheet that returns a value for a row and recalculates as cells change. Use Power Query when you are importing, cleaning, joining, and refreshing datasets as a repeatable process. A merge can add several fields at once, record the transformation steps, and handle data from sources beyond ordinary worksheet ranges. XLOOKUP can be simpler for a small, live worksheet lookup where users expect to see formulas beside the data.

There is also a difference in how duplicates matter. A lookup formula commonly returns one matching result according to its search behavior, while a Power Query merge can represent multiple matching rows and expand them, potentially increasing the output row count. Neither approach fixes an ambiguous key automatically. Before choosing, decide whether the key should be unique and what the correct outcome is if it is not.

Do not confuse Merge with Append. Merge adds columns by matching key values, much like combining related tables. Append stacks rows from one table under rows from another, which is appropriate for combining monthly files that share the same column structure. Use the operation that matches the shape of the data you need.

Frequently Asked Questions

Can Power Query look up a value from another Excel table?
Yes. Load both tables as queries, merge them on a shared key, and expand the column from the lookup query. Choose Left Outer when you want to keep every row from the main table, including rows without a match.

Why does my Power Query merge return null?
The key may be missing from the other table, stored with a different data type, or spelled differently. Check spaces, punctuation, leading zeros, and capitalization, then normalize both key columns consistently.

How do I return more than one column from the lookup table?
After merging, use the expand button on the nested column and select every field you want to add. You can expand several fields in one operation rather than creating a separate merge for each one.

Will a Power Query merge update when I add rows?
It will update when you refresh, provided the new rows are included in the query’s source. Rows added inside a source Excel table are generally included; rows pasted below the table may not be.

Why did the merge create duplicate rows?
The lookup query likely contains more than one row for a selected key. A merge preserves all matches, so expanding a one-to-many match can create multiple output rows. Check the lookup table for duplicate keys and decide how to resolve them.

Is Merge the same as Append in Power Query?
No. Merge matches rows between queries and adds columns; Append stacks rows from queries with compatible columns. Choose Merge for a lookup and Append for combining similar lists, such as separate monthly tables.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.