Continue to WhatsApp?

The chat opens in WhatsApp, in the app or in a new tab. You leave our website, and data such as your IP address is sent to WhatsApp (Meta).

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.

Hands typing on a laptop showing a table with columns and check values used to compare two lists
Photo: Bluestonex on Unsplash

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.

  1. Choose the key column

    Pick a column that identifies each row uniquely and exists in both lists, for example the supplier's item number.

  2. 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.

  3. Pull the values

    Use XLOOKUP, VLOOKUP or Power Query to bring price, lead time or stock from the second list into the first.

  4. Make gaps visible

    Show rows without a match as “not found” instead of leaving them blank. Nobody notices an empty cell.

  5. 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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

The three methods compared
CriterionXLOOKUPVLOOKUPPower Query
Excel versionMicrosoft 365, 2021, 2024all versionsWindows from 2016, Mac only Microsoft 365
Exact matchdefaultonly with FALSEdefault
Lookup directionanyto the right onlyany
Upper and lower caseignoredignoredtreated as different
Duplicate keyfirst matchfirst matchrow is doubled
Repeat next weekcopy formulas down againcopy formulas down againone click on Refresh
Several key columnsvia helper columnvia helper columnbuilt in
Best forquick one-off matchingolder Excel versionsrecurring comparisons
Office employee checking a printed report on a clipboard next to her laptop
Photo: Vitaly Gariev on Unsplash

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.

Typical reasons for a missing match (XLOOKUP)
Your listSupplier listResultCause
10-471110-4711€12.40match
004711 (text)4711 (number)not foundleading zeros lost, text does not match a number
AB-120 with a trailing spaceAB-120not foundinvisible space
K-200k-200€8.90XLOOKUP ignores upper and lower case
5521055210, listed twice€3.10only 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.

Sources and further reading

FAQ

Quick answers.

How do I match two Excel lists and pull values across?

Link both lists through a unique key column, usually the item number. Then use XLOOKUP, VLOOKUP with FALSE or Power Query to bring the values you need from the second list. Show rows without a match as “not found”, count them and check them one by one before you use the result.

How can I compare two lists in Excel without VLOOKUP?

In Microsoft 365, Excel 2021 and Excel 2024, use XLOOKUP. In older versions, use INDEX with MATCH. For recurring comparisons, Power Query's Merge Queries is the better choice. A comparison tool that matches both lists on a key column such as the item number works without any formula at all.

Why does VLOOKUP not find a value that is clearly in the list?

Usually the values differ in ways you cannot see: a trailing space, a non breaking space, or a number stored as a number in one list and as text in the other. Clean both columns with TRIM and SUBSTITUTE and give them the same format. Also check that the last argument is FALSE.

Can I match two Excel tables on more than one column?

Yes. With formulas, add a helper column to both tables that joins the columns with a separator, for example supplier and item number. In Power Query you hold Ctrl and select several columns directly when merging, in the same order in both tables.

Can I compare Excel lists online without uploading my data?

Yes, if the tool processes the files directly in your browser. Our free Excel comparison works that way: your files are not uploaded, and you see new, missing and changed rows. It supports .xlsx and .csv files up to 5 MB and 20,000 rows each. Duplicate keys are flagged as unclear for you to check.

LET'S TALK ABOUT YOUR WORKFLOW

Write to us. We reply personally.

A question, an idea or a task that costs time every week? A few sentences are enough. You get an honest answer from the people who also build your office helper.

Ivan, Lukas & JanReply within 1–2 working days

  • Personal reply
  • No obligation
  • Based in Vienna, GDPR aware
Photo of Ivan
Rather talk directly?Ivan, your contact person+43 676 59 40 027office@pebblebyte.comMon to Fri 08:00–18:00
Have a specific task? Get your free workflow check

Your message

Please do not enter passwords or confidential customer data. We only use your details to handle your request. No newsletter, no sharing for advertising. Details in our privacy notice.