EXCEL AND LISTS
Compare two Excel lists: match them by item number without silent errors
The wholesaler's new price list has arrived, more than 3,000 rows, and the purchase prices need to go into your item list. So you search, copy and paste, one row at a time. And when you are done, there is that nagging feeling that a price ended up in the wrong row somewhere.

It costs more than an afternoon. A wrong purchase price often only shows up once the quote is with the customer or the margin does not add up at year end.
This article shows how to compare two Excel lists so that every row picks up the right value through its item number and every gap becomes visible: with XLOOKUP, with VLOOKUP for older versions and with Power Query for the comparison that comes back every week.
The short answer: how do you match two Excel lists?
Matching two Excel lists means linking every row in list A to the matching row in list B. The link runs through a unique key, usually the item number. You then pull the values you need across and flag every row that has no match.
- Choose the key column
Pick a column that identifies each row uniquely and exists in both lists, for example the supplier's item number.
- Clean the key
Remove stray spaces and give both columns the same format, text or number, so that 004711 and 4711 do not miss each other.
- Pull the values
Use XLOOKUP, VLOOKUP or Power Query to bring price, lead time or stock from the second list into the first.
- Make gaps visible
Show rows without a match as “not found” instead of leaving them blank. Nobody notices an empty cell.
- Check the result
Count the matches, spot check ten rows and only then use the result in your costing or your inventory system.
Which column should you use as the key?
Most comparisons go wrong at the key, rarely at the formula. A key column is the column Excel uses to recognise that two rows belong together. Every formula and every query is only as good as this key. The item number almost always works. The item description almost never does, because “Cable NYM-J 3x1.5” and “NYM-J 3 x 1.5 mm² cable” are two different things to Excel.
- Good keys: supplier item number, EAN or GTIN, customer number, invoice number.
- Weak keys: descriptions, company names, addresses. Spellings differ, so only part of the list matches.
- Your numbers and theirs: keep a column with the supplier's item number in your own item list. Without that bridge there is no key.
What if one column is not unique on its own?
Sometimes only a combination is unique, for example supplier plus item number when two suppliers use the same numbers. Add a helper column with a combined key to both lists: =[@Supplier]&"|"&[@ItemNo]. The separator matters. Without it, “12” and “345” give the same result as “123” and “45”.
Compare two Excel lists with formulas: XLOOKUP, VLOOKUP or INDEX
XLOOKUP in Microsoft 365, Excel 2021 and Excel 2024
XLOOKUP searches for a value in one column and returns the value from the same row of another column. It is available in Microsoft 365, Excel 2021 and Excel 2024. Excel 2016 and 2019 do not have it.
First turn both lists into tables with Ctrl+T and name the supplier table “Supplier”. The formula in your item list is then: =XLOOKUP([@ItemNo],Supplier[ItemNo],Supplier[Price],"not found"). German Excel uses semicolons instead of commas between arguments.
- Exact match is the default. Without further settings, XLOOKUP only returns exact matches.
- The fourth argument is your warning sign. Instead of the #N/A error the cell shows “not found”, which you can filter on.
- Column order does not matter. The item number does not have to sit to the left of the price.
- One formula per extra column. Lead time and stock use the same formula with a different return column.
VLOOKUP and INDEX with MATCH for older versions
If you work with Excel 2016 or 2019, use VLOOKUP. With the supplier list on a sheet called Supplier, the formula is: =IFNA(VLOOKUP(A2,Supplier!$A:$D,3,FALSE),"not found"). The 3 stands for the third column of the range, here the price in column C.
The FALSE at the end is the most important part. Leave it out and, according to Microsoft, VLOOKUP looks for an approximate match. With unsorted item numbers the formula can then return a price from someone else's row, with no error at all. Two more weaknesses: the item number must be in the first column of the range, and if someone inserts a column, the fixed 3 suddenly points to the wrong one.
INDEX with MATCH is sturdier: =INDEX(Supplier!$C:$C,MATCH(A2,Supplier!$A:$A,0)). MATCH finds the row, INDEX fetches the value. The 0 forces an exact match, and inserted columns do no harm.
Merge two Excel lists with Power Query when it comes back every week
Power Query is the Excel tool for fetching and reshaping data, found on the Data tab in the Get & Transform Data group. It is included in Excel for Windows from Excel 2016 onwards. On the Mac it requires a Microsoft 365 subscription, so check there that all the steps below are available. The advantage: you set up the comparison once and refresh it with one click, whoever on the team runs it.
- Load both lists as queries
Click into each table and choose Data > From Table/Range. In the editor, set the data type of the item number to Text in both queries. Microsoft states that the joined columns must have the same data type. If Power Query converts the column to a number automatically, leading zeros are lost.
- Open Merge
Save both queries with Close & Load To > Only Create Connection. Then choose Data > Get Data > Combine Queries > Merge. In the Power Query Editor, Home > Merge Queries does the same. Select your item list on top and the supplier list below.
- Select the key
Click the item number column in both tables. For a combined key, hold Ctrl and select several columns, in the same order in both tables.
- Choose the join kind
Choose Left Outer: all rows from the first table, matching rows from the second. All your items stay, and matching supplier values are added. At the bottom of the dialog, Excel shows how many rows match. That number is your first check.
- Expand and load
Click the expand icon (double arrow) on the new column, pick price and lead time and load the result to a new sheet with Close & Load.
Next week you only replace the data in the supplier table and click Data > Refresh All. Unlike the formulas, Power Query treats upper and lower case as different. And a duplicate key in the supplier list doubles the affected row after expanding, instead of quietly taking the first match. The join kind “Left Anti” returns exactly the items without a counterpart. More on that in the article on finding missing records.
| Criterion | XLOOKUP | VLOOKUP | Power Query |
|---|---|---|---|
| Excel version | Microsoft 365, 2021, 2024 | all versions | Windows from 2016, Mac only Microsoft 365 |
| Exact match | default | only with FALSE | default |
| Lookup direction | any | to the right only | any |
| Upper and lower case | ignored | ignored | treated as different |
| Duplicate key | first match | first match | row is doubled |
| Repeat next week | copy formulas down again | copy formulas down again | one click on Refresh |
| Several key columns | via helper column | via helper column | built in |
| Best for | quick one-off matching | older Excel versions | recurring comparisons |

A purchasing example: pulling prices from the supplier list
The table shows why individual rows fail in this example. Expect cases like these in any file exported from someone else's system.
| Your list | Supplier list | Result | Cause |
|---|---|---|---|
| 10-4711 | 10-4711 | €12.40 | match |
| 004711 (text) | 4711 (number) | not found | leading zeros lost, text does not match a number |
| AB-120 with a trailing space | AB-120 | not found | invisible space |
| K-200 | k-200 | €8.90 | XLOOKUP ignores upper and lower case |
| 55210 | 55210, listed twice | €3.10 | only the first match is returned, the second price is lost |
Why does VLOOKUP not find a value? Common mistakes and how to avoid them
- Approximate match: VLOOKUP without FALSE can return wrong prices with no error. Check existing files for that fourth argument.
- Number versus text: an item number stored as a number will not find the same number stored as text. Align both columns, for example with =TEXT(A2,"000000") for six digit numbers with leading zeros.
- Spaces: TRIM removes extra spaces, including leading and trailing ones. According to Microsoft, the non breaking space (character 160) from web exports stays. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) instead.
- Duplicate keys: the formula quietly takes the first match. Check the key with COUNTIF beforehand.
- Fixed ranges: a range such as $A$2:$D$2500 does not grow. New items from row 2501 are never found. Tables created with Ctrl+T grow automatically.
- Formulas as the final result: if someone deletes the supplier list, every formula shows an error. Paste the checked result as values before you pass it on or import it.
When does manual matching stop paying off?
For a one-off comparison, a formula is enough. Repetition is what costs money. A cautious estimate for a single supplier: open the file, adjust columns, copy formulas, chase gaps. That easily takes 45 minutes a week. Over 46 working weeks that is 34.5 hours a year. At an assumed internal cost of €38 per hour, meaning wages plus overheads, the routine costs about €1,310 a year. Errors are not included yet, such as an outdated purchase price in a quote.
You can run your own numbers in two minutes with the office work cost calculator. The maths usually tips when one of these applies:
- Several suppliers send lists in different formats with different column names.
- The result then has to be imported into your inventory or ERP system.
- Only one person in the company knows how the file works. When she is on holiday, the comparison waits.
- Lists arrive as PDF or in the body of an email, not as Excel files.
- Price changes must not only be copied but checked and approved. That needs its own checks, see comparing supplier price lists.
The next step: compare lists without formulas or hand the job over
When the list comes from a supplier, the easiest place to start is our free price list comparison. Drop in the old and the new list as Excel, CSV or PDF. The tool matches them on the item number, even when the columns have different names or a different order, and restores leading zeros that Excel removed. You see every price change as a percentage plus new and discontinued items, and you can download the changes as a list. It does not guess duplicate item numbers; it shows them under "Please check". For other lists, such as customers or stock levels, use the Excel comparison. It shows missing, new and changed rows with the old and new value. Writing values into your own list is still a job for the formula or Power Query. With both tools, your files stay in your browser.
If the comparison comes back every week with several suppliers, an office helper, our name for a small custom tool, takes over the whole chain: fetch the list from the mailbox, clean the keys, match, create the import file for your inventory system. Unclear rows go to a person who approves them. Your software stays as it is, and the process no longer depends on a single colleague. The page on automating Excel work shows what that looks like.
If several suppliers send new lists every week, it is worth looking at the whole workflow. In the free workflow check, describe which lists arrive and where the result has to go. Within 1–2 working days you get a personal, free assessment of whether a formula, Power Query or an office helper fits best. An office helper costs from €1,990 excl. VAT at a fixed price, and small projects are ready to use 3–7 days after the use case is agreed.
