VLOOKUP in Power Query with Merge Queries

In short: load both tables as queries. In the first query, select the key column, then Home > Merge Queries, pick the second query and its key column, join kind Left Outer. Then click the expand icon on the new column and tick only the columns you want.

Try it now: how a merge works

The sheet holds orders on the left and a price list on the right, both loaded as queries. Get the price for every order without writing a single formula.

This lesson compares Power Query with VLOOKUP from Level 3 in a few places. If you have not met VLOOKUP yet, that is fine: everything the exercises need is explained on this page.

A merge happens in two steps, and Applied Steps shows them separately:

1. Merged Queries (Table.NestedJoin): every row of the first query gets a new column. It holds a small table with every row from the second query that matches the key. The preview just says "Table".
2. Expand (Table.ExpandTableColumn): you choose which columns to pull out of that small table. Skip the second query's key column, you already have it.

Task Orders are on the left, the price list on the right (both loaded as queries). Get the price for every order from the price list: Merge Queries on Item, then expand only the Price column.

	A	B	C	D	E
1	Item	Qty		Item	Price
2	A-100	5		B-200	12
3	C-300	2		A-100	4
4	B-200	10		D-400	30
5	A-100	3		C-300	7
6	D-400	1		E-500	9
7	B-200	4

Merge and VLOOKUP side by side

VLOOKUP	Power Query
lookup_value	key column of the first query
table_array	second query
col_index_num	ticked columns when expanding, by name instead of number
FALSE (exact match)	joins always match exactly
#N/A when nothing matches	null

When you expand, Excel offers Use original column name as prefix. With it switched on, the new column is called something like "Prices.Price". Untick it and the column keeps its plain name "Price", as in the exercise.

Picking the join kind

For a VLOOKUP-style lookup the join kind is always Left Outer (all from first, matching from second). Orders without a price stay in the list and get null. Other join kinds behave differently: an inner join keeps only rows with a match, and an anti join keeps only rows without one, which is handy for finding customer IDs that are missing from your master data.

One more exercise: several columns and a missing customer

This time you bring back two columns at once. One customer ID does not exist in the customer list, so where VLOOKUP would show #N/A, the merge shows null.

Task Get Company and Region for every revenue row from the Customers query. Customer ID K-9 is missing from Customers; that row must stay anyway (with null).

	A	B	C	D	E	F
1	Customer ID	Revenue		Customer ID	Company	Region
2	K-1	1200		K-2	Roth GmbH	South
3	K-3	800		K-4	Weber KG	North
4	K-2	450		K-1	Schmidt AG	North
5	K-9	300		K-3	Vogel OHG	West
6	K-1	990
7	K-4	610

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

The big difference: multiple matches

VLOOKUP and XLOOKUP always return the first match. A join returns all of them. If an item appears twice in the price list, the order turns into two rows when you expand. That is not a bug; it reveals duplicate keys in your lookup table. Remove them there first if every order must appear exactly once.

Common mistakes

Every value is null

The keys do not match. Typical causes are edge spaces, different capitalization, or one key column being text while the other is a number. Clean both key columns and give them the same data type.

The row count goes up

The key is not unique in the second query, see above.

The column is called "Prices.Price"

The prefix option was on when you expanded. Rename the column, or untick the option next time.

In German Excel

The German editor calls the command Abfragen zusammenführen, the join kind Linke äußere Verknüpfung, and the steps show up as "Zusammengeführte Abfragen" and "Erweiterte …". German tutorials usually file this topic under "Power Query SVERWEIS".

Which Excel versions support this?

Merge Queries is part of the Power Query Editor in Excel 2016 and later for Windows and in Microsoft 365. On a Mac it needs Microsoft 365. See the Power Query overview for details.

FAQ