Power Query VLOOKUP: How to Match Tables in Excel Instead

Power Query does not have a VLOOKUP function; use Merge Queries to match rows from two tables and bring a column of results into the first. In Excel for Microsoft 365 on Windows, load both tables into Power Query, merge them on their shared key, expand the result column, then select Home > Close & Load.

What people mean by Power Query VLOOKUP

When someone asks for a Power Query VLOOKUP, they usually want the same outcome as a lookup formula: find a value in one dataset using an identifier from another, then return a related field. For example, an orders table might contain product IDs but not product names. A product table contains both IDs and names. Matching the tables adds the product name to each order.

In a worksheet, VLOOKUP searches down the first column of a selected range and returns a value from another column in that range. Power Query takes a different approach. Its merge operation joins two queries using one or more matching columns. You can then expand selected fields from the second table into the first. The result is a transformed dataset, not a formula filled down individual worksheet rows.

This distinction matters when deciding how to work. A formula recalculates as worksheet cells change. A Power Query result updates when you refresh the query. Power Query is particularly useful when the same cleanup and matching steps must be repeated for new monthly files or refreshed source data.

How a Power Query merge works

A merge compares the values in selected columns from two queries. Those columns are often called keys: examples include product ID, customer number, or an exact email address. Power Query creates a new column containing matching rows from the second query. That column initially holds nested table values; expanding it selects the fields you want to add.

The merge dialog also asks for a join kind. For a lookup-style task, the usual choice is Left outer: keep every row from the first, or left, table and include matching rows from the second table. If no match exists, the expanded result is null, Power Query’s representation of a missing value.

Unlike a typical VLOOKUP set to exact match, a merge can return more than one matching row. If the lookup table has duplicate keys, one original row may become several rows after expansion. That can be correct for a one-to-many relationship, but it is often an accidental source of duplicated totals. Check whether the key is unique before merging.

Steps to replace VLOOKUP with Merge Queries

Prepare the source ranges. Make each dataset an Excel Table: select a cell in the range and choose Insert > Table, or press Ctrl+T on Windows. Confirm that the table has headers. Clear empty rows within the data and give the tables meaningful names on the Table Design tab.
Load both tables into Power Query. Select a cell in the first table and choose Data > From Table/Range. In the Power Query Editor, review the column headers and data types. Repeat for the second table. Each source should appear as a query in the editor.
Start the merge. In Power Query Editor, select the query you want to retain as the primary result. Choose Home > Merge Queries to add the merge to that query, or Home > Merge Queries as New to create a separate output query.
Select the matching columns. Choose the second query in the dialog, then click the key column in each preview. For multiple key columns, select corresponding columns in the same order in both previews. Choose Left outer (all from first, matching from second) for the usual lookup behavior.
Expand the matched data. Select OK. In the new column, select the expand icon, choose the fields to bring in, and optionally clear the option to prefix expanded column names. Rename columns if needed so the output is clear.
Load the result. Choose Home > Close & Load to return the result to Excel. You can also use Home > Close & Load To to choose a worksheet location or another available destination.

In Excel for Mac, Power Query is available in Microsoft 365, but connector support and some interface details can differ from Windows. Look for the Power Query commands on the Data tab; if a particular connector or command is missing, check the current Excel for Mac feature availability. Excel for the web does not offer the same full Power Query authoring experience as desktop Excel.

Example: adding product names to orders

Suppose an Orders table has Order ID, Product ID, and Quantity columns. A Products table has Product ID, Product Name, and Category. Merge Orders with Products using Product ID as the key, choose a left outer join, and expand Product Name and Category. The output keeps the order rows and adds product details wherever the IDs match.

For example, if an order has Product ID P-104 and the product table maps P-104 to “Desk lamp” in the Product Name column, the merged row includes Desk lamp. If P-104 appears twice in Products, expanding the merge can produce two rows for that order. If it does not appear, the product name is null. Those outcomes help identify duplicate or missing reference data rather than silently hiding them.

For a small, one-off lookup directly in the worksheet, a formula may be simpler. With the lookup value in A2 and a mapping table where IDs are in F2:F100 and the returned product names are in G2:G100, enter =VLOOKUP(A2,$F$2:$G$100,2,FALSE). If A2 contains P-104 and the matching row in the mapping table has Desk lamp in column G, the formula returns Desk lamp. FALSE requests an exact match. In Microsoft 365 or Excel 2021 and later, XLOOKUP is another worksheet option, but it is still a formula rather than a Power Query operation.

Choosing the right join type

The join kind determines which rows survive the merge. Left outer is the closest general equivalent to adding a lookup result while preserving the original list. Other kinds are useful for different questions:

Right outer: keep every row from the second table and matching rows from the first. This is less common for a lookup-style result because it prioritizes the other source.
Full outer: keep rows from both tables, including unmatched rows. Use it to compare two lists or find records present in only one source.
Inner: keep only rows with a match in both tables. This removes unmatched records, so it is not equivalent to a lookup that preserves every original row.
Left anti or right anti: return rows from one side that have no match on the other. These are useful for finding missing IDs or investigating gaps in a reference list.

Choose based on the question you need the output to answer, not simply on which option sounds most inclusive. For example, a left anti join from Orders to Products can list order IDs that do not exist in the product catalogue. It is often a clearer diagnostic than loading a large merged table and scanning for nulls.

Refresh behavior and repeatable work

Once loaded, a Power Query result is a snapshot of the source data from the last refresh. If the source table changes, the worksheet result does not necessarily change immediately. Refresh the query using Data > Refresh All, or right-click the query output and choose Refresh. The saved query records the transformation steps, so a refresh reapplies them to the current source data.

This repeatability is the main advantage over copying a new formula down every time a report arrives. If next month’s orders are added to the source table, a refresh can update the merged output without rebuilding the merge. Check that new rows are included in the source table or source file; a query cannot match records it was not configured to read.

Refresh settings and source locations also matter. A query that reads a local workbook path may fail if that file is moved or renamed. For a shared process, keep source files in a stable location and document which table or file the query reads. For workbooks with several linked queries, use Refresh All when you want dependent results updated together.

Common matching problems

A merge that unexpectedly returns nulls, duplicates, or too many rows is usually telling you something about the source data or key selection. Before changing the join type, check these common causes:

Different data types: one key may be text while the other is a number. In Power Query, select each key column and choose the same appropriate data type from the data type control. Identifiers with leading zeros should generally be treated as text so that values such as 00127 retain their meaning.
Spaces or inconsistent characters: a key can look identical but contain leading or trailing spaces. Use Transform > Format > Trim on text keys when appropriate. Clean inconsistent punctuation or capitalization only when the business rules say those differences should not matter.
Wrong key columns: matching on a name can be unreliable if names are not unique or are spelled differently. Prefer a stable identifier, and select the corresponding columns in the same order when using a composite key.
Duplicate keys in the lookup table: count or inspect repeated IDs before expanding. Decide whether duplicates should be removed, consolidated, or treated as legitimate multiple records; removing them without understanding the data can discard valid information.
Null or blank keys: blank identifiers cannot provide a meaningful match. Filter or investigate them according to the reporting requirement rather than assuming they represent the same real-world entity.

Power Query also offers fuzzy matching for some text merges, but it is not a safe substitute for a reliable identifier. Similarity-based matching may pair names that look alike but refer to different entities. Use it only when approximate text matching is genuinely appropriate, review the results, and do not treat a fuzzy match as proof that two records are the same.

Power Query merge versus VLOOKUP

Use VLOOKUP when the task is a simple, immediate worksheet lookup and you want the result to recalculate as cells change. It is familiar and easy to inspect in a small table. Its traditional structure, however, relies on a return-column number and requires the lookup column to be the first column of the selected range. In modern Excel, XLOOKUP can avoid some of those limitations, but it remains cell-based.

Use Power Query when you repeatedly combine data, need several transformation steps, or want to keep the matching process separate from the final worksheet. A merge can add multiple columns at once and is easier to reuse as a refreshable workflow. It does not replace formulas for every situation: the output updates on refresh, and the query setup may be unnecessary overhead for a single small lookup.

Neither method automatically fixes poor source data. Both depend on choosing the right key and understanding duplicate records. The practical choice is often based on scale and repeatability: formulas suit quick, live worksheet calculations; Power Query suits staged data preparation that you expect to run again.

Frequently Asked Questions

Is there a VLOOKUP function in Power Query?
No. Power Query uses Merge Queries to join data from two queries. It can produce a similar result by adding matching fields to a table, but the steps and refresh behavior differ from a worksheet VLOOKUP formula.

Which join should I use to make a Power Query lookup?
Choose Left outer (all from first, matching from second) when you want to preserve every row in your main table and add information where a match exists. Use a different join only when you deliberately want to keep or exclude unmatched rows.

Why does my merge create duplicate rows?
The matching key may occur more than once in the second table. A merge can return every matching row, unlike a lookup that typically returns one result. Check duplicates and decide whether they are errors or valid one-to-many relationships before removing anything.

How do I find IDs that did not match?
Use a Left anti join from the main table to the reference table to return main-table rows with no match. Alternatively, use a left outer merge and inspect the expanded column for nulls, especially when you also need the matched records.

Does a Power Query result update automatically when source cells change?
Not in the same way as a worksheet formula. Refresh the query or choose Data > Refresh All to rerun its saved steps against the current source. Confirm that the changed rows are included in the query’s source.

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.