Excel Power Query does not run the VLOOKUP function inside a query; instead, use Merge Queries to match rows in two tables and bring selected columns into the result. Choose the matching key in each table, select a join type such as Left Outer, expand the returned columns, then load the result and refresh it when the source data changes.
What people mean by Power Query VLOOKUP
The phrase usually describes a lookup task, not a special Power Query function. In a worksheet, VLOOKUP searches for a value in the first column of a range and returns a value from another column in that same range. Power Query performs a comparable task by joining two data sets on one or more matching columns. The operation is called a merge.
For example, an Orders table might contain order numbers, dates, and product codes, while a Products table contains each product code, name, and unit price. Merging Orders with Products on ProductCode lets you add the product name and price to each order row. The merged result can be refreshed after either source table changes, without copying a formula down a worksheet column.
This distinction matters: a merge is a series of saved data-preparation steps, not a live cell formula. It is useful for repeatable imports and larger refresh workflows, while a worksheet lookup may be more convenient for a small, immediately updated calculation.
Prepare the source tables
Power Query works most cleanly when each source is an Excel Table with a header row. Convert each range by selecting a cell in it and choosing Insert > Table, or use Ctrl+T on Windows (Command+T on Mac). Confirm that the table has headers, then give it a clear name on the Table Design tab, such as Orders and Products. Avoid blank header names and merged cells.
Before merging, check that the key columns really represent the same kind of identifier. ProductCode in one table and SKU in another can be matched if their values correspond, but a product description should not be used as a substitute for a stable code when codes are available. Check for differences such as leading or trailing spaces, inconsistent punctuation, and numbers stored as text in one table but as numbers in the other. Power Query may not match values whose underlying types or contents differ.
- Make sure the key is populated for rows that should match.
- Check that the lookup table has one row per key if you expect one result per order.
- Set both matching columns to compatible data types in Power Query before merging.
- Keep a copy of the original source data; the query can transform imported data without changing that source.
Merge tables step by step
For the Windows Microsoft 365 workflow, first turn both ranges into tables. Select a cell in the first table and choose Data > From Table/Range. The Power Query Editor opens. Repeat for the second table, so both appear as queries in the editor. If a query already exists, open it from Data > Queries & Connections.
- In Power Query Editor, select the query that should keep its rows, such as Orders.
- Choose Home > Merge Queries. Choose Merge Queries as New instead if you want to create a separate result and leave the original query unchanged.
- In the merge dialog, select the second query, such as Products. Click the key column in the first preview and the corresponding key column in the second preview. For a multi-column key, select the columns in the same order in both tables, using Ctrl-click on Windows.
- Select the join kind. For a lookup that should retain every order, choose Left Outer (all from first, matching from second). Select OK.
- In the resulting column, click the expand button in its header. Select the fields to bring across, such as ProductName and UnitPrice. Clear the option to use the original column name as a prefix if you prefer shorter headers, then confirm.
- Review the preview and choose Home > Close & Load to load the query result to Excel. Use Close & Load To when you need to choose a destination or connection-only output.
The merge step initially creates a nested table column; it does not immediately display every field from the second table as a normal column. Expanding that column is the step that selects the returned values. Keep only fields the report needs to make the loaded table easier to read and maintain.
Choose the right join type
Join type controls which rows survive the merge. This is one of the biggest differences between a casual worksheet lookup and a data join: a join can intentionally preserve or exclude unmatched rows, rather than merely returning a blank for a missing lookup.
- Left Outer keeps every row in the first query and adds matching values from the second. Use this for the common VLOOKUP-style case where every order must remain, even if a product code has no match.
- Inner keeps only rows with a match in both queries. This can be useful when the output should contain only records with a valid corresponding product.
- Full Outer retains rows from both queries, matching related rows where possible. It helps reconcile two lists and identify items found on only one side.
- Left Anti returns rows from the first query that have no match in the second. Use it to find order codes missing from a product list. Right Anti does the equivalent for rows only in the second query.
Right Outer is also available, but if the required result is naturally based on the second table, it is often clearer to make that table the first query and use Left Outer. The first query is the side whose rows a Left Outer join preserves.
Duplicates, missing matches, and key quality
A frequent surprise is that a merge can increase the number of rows. Suppose Orders contains one row for SKU A17, but Products contains two rows for A17 because the product list has a duplicate. A Left Outer merge finds both matches, and expanding the nested table produces two output rows for that order. This is not Power Query randomly duplicating data; it is returning every matching row.
If one lookup result is intended per key, remove or resolve duplicate keys in the lookup table before merging. In Power Query, select the key column and choose Home > Remove Rows > Remove Duplicates, but do this only if duplicates are genuinely redundant. If the rows represent different regions, dates, or versions, decide which one should apply instead of removing information blindly.
With a Left Outer join, unmatched rows remain and the expanded lookup fields show null. That indicates no matching row was found; it does not automatically mean the source cell was empty. Check for spelling differences, extra spaces, incompatible data types, or a missing key in the lookup table. For text identifiers that should be compared without regard to letter case, standardize the key columns consistently before merging, for example by applying the same case transformation to both. Do not alter codes where capitalization is meaningful.
Power Query merge compared with VLOOKUP
VLOOKUP can be sufficient when you need a quick worksheet result and the lookup range is stable. For example, with the search SKU in A2 and a product table in F2:H100 where column F is SKU, G is product name, and H is price, =VLOOKUP(A2,$F$2:$H$100,3,FALSE) returns the value from the third column for an exact match. If A2 contains P-104 and that row has a price of 12.5 in column H, the result is 12.5. The FALSE argument requests an exact match; without it, VLOOKUP can use approximate-match behavior under the function’s rules.
Power Query is often a better fit when the same import and cleanup must be repeated, when sources come from files or databases, or when you want to document transformations as named steps. A merge also lets you match on columns that are not the first column of a selected range, choose among several join types, and add multiple fields at once. It can match on multiple columns, provided the selected key columns are aligned in the same order.
Neither method is universally better. A worksheet formula responds as its referenced cells recalculate, while a query output updates when the query refreshes. VLOOKUP may be easier for a small, interactive model; Power Query may be easier to audit and repeat for a recurring data-preparation job. Excel 365 also has XLOOKUP, which can look in one column and return from another without VLOOKUP’s column-index number, but it remains a worksheet formula rather than a Power Query merge.
Refresh and maintain the result
After loading the query, refresh it to bring in changes from its sources. Use Data > Refresh All to refresh workbook connections, or right-click the output table and choose Refresh. You can also open Data > Queries & Connections, right-click a query, and select Refresh. The exact refresh options depend on the query’s source and connection settings.
Refresh is not the same as editing the source data through the result. A query output is generated from its source and transformation steps, so manually changing a value in the loaded result may be overwritten on the next refresh. Make corrections in the original source or add a deliberate transformation in the query. If a source file is moved or renamed, the query may need its source path updated before it can refresh.
For a recurring process, give queries and columns descriptive names, remove unused columns early, and inspect the applied steps in the editor when the source structure changes. If another person will use the workbook, note where the source data lives and how to refresh it. In Excel for Mac, Power Query is available in Microsoft 365, but connectors and authoring features can differ by release; the ribbon layout may also differ from the Windows instructions above. Excel for the web has more limited query editing, so use the desktop app when you need the full editor workflow.
Common mistakes to avoid
- Picking the wrong key: matching on a non-unique name can attach the wrong record or multiply rows. Prefer a unique ID.
- Choosing Inner by habit: it removes unmatched rows from the first table. Use Left Outer when all of those rows must remain.
- Expanding every field: unnecessary columns make the output harder to use and can create duplicate column names. Select only what is needed.
- Assuming the output updates immediately: a query result generally changes on refresh, not whenever a source cell is edited.
- Ignoring data types: a numeric identifier and a text identifier can look alike on screen but fail to match. Standardize types and values on both sides.
Frequently Asked Questions
Can I use VLOOKUP inside Power Query?
No. VLOOKUP is an Excel worksheet function, not a Power Query operation. Use Merge Queries to join tables, or load the data to a worksheet and use a lookup formula there.
Does Power Query merge update automatically?
Not in the same way that a worksheet formula recalculates after a cell edit. Refresh the query, or use Data > Refresh All, to rerun its steps against the current source data.
Why does my merged table have more rows than the first table?
The second table probably has multiple rows for at least one selected key. Expanding a match returns each matching row. Check duplicates in the lookup table and decide which record should be retained.
What does null mean after a merge?
For a Left Outer merge, null in an expanded field usually means that no matching row was found in the second query, or that the matched source field itself is blank. Check the key values and data types to tell which case applies.
Can Power Query match using two columns?
Yes. In the merge dialog, select the first key column in each preview, then select the second key column in the same order. Both values together form the match key.
Should I use Power Query or XLOOKUP?
Use XLOOKUP for a direct worksheet calculation that should respond to cell changes. Use Power Query when you want a repeatable import-and-transform process, multiple returned fields, or a join that is refreshed as a data workflow.
Related
- Vertex42 Budget Spreadsheet: How to Choose and Use One
- Excel Power Query: How to Group Data
- Vertex42 Debt Snowball Spreadsheet: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Excel Sample Data: Examples That WorkFor a basic SUMIF Excel sample, put item names in A2:A6 and amounts in B2:B6; to total Apples,…
- Profit and Loss Statement in Excel: Build It RightTo make a profit and loss statement in Excel, organize income and expense transactions in a dated ledger,…
- Excel Growth Rate Formula: Calculate Change Over TimeTo calculate growth rate in Excel, divide the change between the new and old values by the old…
- XLOOKUP in Excel: Compare Two Lists and Find MatchesYes. In Excel, XLOOKUP can compare a value in one list with another and return the matching item…
- Excel Vector Analysis: Operations, Formulas, and UsesExcel has no dedicated “vector analysis” tool or vector data type, but it can perform common vector calculations…
- Concat Excel Cell Range: Join Cells With Simple FormulasWith the values to join in A2:A5, enter =CONCAT(A2:A5) to combine the range into one text string; for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.