Power Query VLOOKUP: Match Data From Another Table

Yes. In Excel for Microsoft 365, Power Query can look up values in another table by merging the two queries: choose a matching key column, select a join type, and expand the fields you want to bring into the first table. The closest Power Query equivalent to a VLOOKUP that keeps every row from your main table is a Left Outer merge.

What a Power Query lookup does

VLOOKUP searches for a value in one range and returns a value from another column in the same range. Power Query uses a different model: it combines two tables by matching values in one or more columns. This operation is called a merge. After the merge, you choose which columns from the second table to add to the result.

For example, suppose an Orders table contains OrderID, CustomerID, and Amount, while a Customers table contains CustomerID and CustomerName. Merge Orders with Customers using CustomerID, then expand CustomerName. The result keeps the order details and adds the customer name to each matching order.

A merge does not alter the original source tables. It creates a query result that can be loaded to a worksheet or kept as a connection. When source data changes, refresh the query to rebuild the result. This makes Power Query useful when you repeat the same cleanup and matching steps on updated files.

Prepare both tables before merging

Power Query works most reliably when each source is a structured Excel table with a clear header row. On the Windows desktop, click inside each data range and choose Insert > Table, or press Ctrl+T, then confirm that the table has headers. Give the tables recognizable names on the Table Design tab, such as Orders and Customers. Avoid merged header cells, blank header names, and totals rows inside the data.

Bring each table into Power Query as a separate query. Click in the first table and choose Data > From Table/Range. In the Power Query Editor, choose Home > Close & Load To if you want to control where the result is loaded; for a source query that you will only use in a merge, a connection-only load can keep the worksheet uncluttered. Repeat for the second table.

The lookup key must represent the same kind of value in each table. CustomerID is a suitable key if it identifies a customer consistently. A customer name may be less reliable because spelling, punctuation, or spacing can vary. Before merging, check that both key columns have compatible data types, such as Text on both sides or Whole Number on both sides.

Merge the queries step by step

In Excel, open the query that represents the table whose rows you want to preserve. In the Power Query Editor, select Home > Merge Queries. Choose Merge Queries as New instead if you want a separate result query and prefer to leave the original query unchanged.
In the Merge dialog, choose the second query from the lower table dropdown. Click the key column in the first preview and then the matching key column in the second preview. A selected column is highlighted; for a multi-column key, select the columns in the same order in both previews, using Ctrl-click for additional columns.
Choose the join kind. For a lookup-style result, select Left Outer (all from first, matching from second). This keeps every row from the first query and attaches matching records from the second.
Check the preview and the match indicator, then select OK. Power Query adds a column containing nested tables from the second query. It has not yet placed the lookup fields directly into the rows.
In the new column header, click the expand button, shown as a two-arrow icon. Select the fields to include, such as CustomerName, and clear the option to use the original column name as a prefix if you want shorter headings. Select OK.
Review the resulting columns and choose Home > Close & Load, or Close & Load To, to send the result to Excel.

For the Orders and Customers example, the expanded result has one row per order when each CustomerID appears only once in Customers. Each order retains its original columns and gains the selected customer field.

Choose the right join kind

The join kind determines which rows survive the merge. It is one of the most important choices because a merge can appear to work while quietly removing records or adding unexpected ones.

Left Outer: Keeps all rows in the first query and matching rows from the second. Unmatched rows remain, with null values in the expanded lookup fields. This is usually the closest match to a VLOOKUP copied down a main list.
Inner: Keeps only rows with a match in both queries. Use it when unmatched primary records should be excluded, not when you need a complete list from the first table.
Right Outer: Keeps all rows in the second query and matching rows from the first. It can be useful when the second table is the one whose full list matters, although swapping the query order and using Left Outer is often easier to explain.
Full Outer: Keeps rows from both queries, including unmatched rows on either side. This can help reconcile two lists, but the result may contain nulls in fields belonging to the table with no match.
Left Anti or Right Anti: Returns rows from one side that have no match on the other. These joins are useful for finding missing IDs or records that need investigation.

Understand keys, duplicates, and unmatched rows

A lookup key should identify the intended record. If the second table has duplicate keys, a merge may return more than one matching row for a single row in the first table. Expanding the nested results can therefore multiply rows. For example, if CustomerID 104 appears twice in Customers, an order for customer 104 can appear twice after expansion. That is not necessarily a Power Query error; it reflects multiple matches in the source.

Check whether duplicates are legitimate before removing them. If the lookup table should have one record per key, investigate why it does not. You can select the key column and use Home > Remove Rows > Remove Duplicates, but this keeps one occurrence and may discard meaningful information. If duplicate records represent different dates or categories, define a rule for choosing the correct record before merging.

Unmatched rows in a Left Outer merge usually show null in the expanded fields. Common causes include a missing ID, an ID stored as text in one table and as a number in the other, leading or trailing spaces, or inconsistent formatting such as 00104 versus 104. For text keys, select the column and use Transform > Format > Trim to remove leading and trailing spaces. Do not strip leading zeros if they are meaningful parts of an identifier.

When a key is made from multiple fields, such as Region plus AccountNumber, select both columns in each preview in the same order. Alternatively, create a carefully defined combined key in both queries. A multi-column match is generally safer than matching on a name alone, provided each component is cleaned and typed consistently.

Refresh and maintain the result

A Power Query merge is a set of saved transformation steps, not a live formula that recalculates every cell as soon as a source value changes. After changing source data, use Data > Refresh All in Excel to update dependent queries. You can also right-click a query output table and choose Refresh. The refreshed output reflects the latest source data and the saved merge and expansion steps.

If users add rows to an Excel Table, the table normally expands to include them, and the query can read those added rows on refresh. A query based on a fixed range or an external file may behave differently: confirm that its source includes the new rows and that the file is available. Refresh settings can be adjusted through Data > Queries & Connections; the exact options depend on the query and workbook setup.

Choose Close & Load To when you need to decide whether the result should appear in a worksheet, be added to the Data Model, or remain connection-only. Loading a large output to a worksheet can make the workbook harder to navigate, while a connection-only query can still serve as an input to another query.

Power Query merge compared with VLOOKUP

Use VLOOKUP when you need a quick, formula-based answer in a worksheet and the source data is already laid out for a simple match. Use Power Query when you regularly import, clean, combine, or reshape data and want those steps to be repeatable after refresh. Power Query can also combine multiple columns as keys and offers join types that make unmatched records easier to analyze.

The two approaches differ in how they respond to changes. A VLOOKUP formula recalculates based on workbook calculation settings and its references; a Power Query result changes when the query refreshes. A formula can be convenient for an interactive worksheet, while a merge is often more manageable for a recurring report with many preparation steps.

Neither method automatically fixes poor data. A VLOOKUP can return an unintended first match when the lookup key is duplicated, and a Power Query merge can expand to multiple rows for duplicate keys. In both cases, validate that the lookup table has the structure and uniqueness the task requires.

Common problems and practical checks

No matches appear: Compare data types, spaces, leading zeros, and the actual key values in both previews. Confirm that you clicked the intended columns rather than similarly named fields.
The output has more rows than expected: Check for duplicate keys in the second query. Count occurrences of the key or group by it to find repeated values before deciding how to handle them.
Some records disappear: Confirm the join kind. An Inner join removes unmatched first-table rows; a Left Outer join preserves them.
New source rows do not show up: Refresh the query and confirm the source is an Excel Table or a range that includes the additions. Also check that the query points to the expected sheet, table, or file.
The result has too many columns: Expand only the fields needed from the second query. Rename fields that would otherwise have confusing or repeated headings.

Power Query also offers fuzzy matching for certain text merges. It can help with approximate text variations, but it should not replace a dependable ID when one exists. Similar names can refer to different people or businesses, and approximate matches need review before they are treated as correct.

Frequently Asked Questions

Can Power Query do a VLOOKUP between two worksheets?
Yes. Convert each worksheet’s data range into an Excel Table, create a query from each table, and merge the queries on the shared key. The tables can be on different sheets in the same workbook.

Which Power Query join is the same as VLOOKUP?
A Left Outer join is usually the closest equivalent when the first query is the main list: it retains every first-table row and brings in matching data from the second. Unmatched rows show null in the expanded fields.

Why does a Power Query merge create duplicate rows?
The second query may contain more than one row for a key. Expanding the match returns each matching record, which can multiply the first-table row. Check the duplicates and decide whether to correct, filter, group, or otherwise resolve them.

Does a Power Query lookup update automatically?
Not in the same way as a worksheet formula. The query result updates when you refresh it, for example with Data > Refresh All. You can configure refresh behavior, but do not assume a source edit immediately changes the loaded result.

Can I merge tables using more than one column?
Yes. In the Merge dialog, select each matching column in the same order in both table previews, using Ctrl-click to select additional columns. Confirm the data types and values in every part of the combined key.

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.