BOM comparison - excel function

Dušica Garović 10 Reputation points
2026-08-13T06:58:27.5666667+00:00

I need to check if data from the current BOM ERP(article + quantity) is the same or have some differences from the master file (article + quantity).

I tried to do it with xlookup/if function but every time some error occurs, as it can be visible in the photo 2. For both equal data, function shows that the values are different. I don't know what can be the cause when all the data has the same format. User's image

User's image

Microsoft 365 and Office | Excel | For business | Other
0 comments

6 answers

Sort by: Newest
Most helpful Newest Oldest

  1. AI answer

    2026-08-13T06:58:57.4433333+00:00

    Use one lookup key that combines article + quantity, then compare that key between the two lists. This avoids false mismatches when the article exists but the quantity differs, or when data types are inconsistent.

    Example approach:

    1. Add a helper column in both tables that joins item and quantity.
      • In the ERP table: 
             =A2&"|"&B2
      • In the Master table: 
             =D2&"|"&E2
    2. Check whether each ERP row exists in the Master list: 
         =IFERROR(XLOOKUP(A2&"|"&B2,$D$2:$D$9&"|"&$E$2:$E$9,$D$2:$D$9,"Not found"),"Not found")
      A simpler result-only check is: 
         =IF(IFERROR(XLOOKUP(A2&"|"&B2,$D$2:$D$9&"|"&$E$2:$E$9,$D$2:$D$9,""),"")="","NOK","OK")
    3. Do the reverse check for the Master rows against ERP: 
         =IF(IFERROR(XLOOKUP(D2&"|"&E2,$A$2:$A$7&"|"&$B$2:$B$7,$A$2:$A$7,""),"")="","NOK","OK")

    If the current formula shows different values even when they look the same, the common causes in Excel are:

    • text vs number mismatch
    • extra spaces or hidden characters
    • incorrect lookup/return ranges
    • exact match not actually finding an identical value

    To fix that, normalize the data first:

    • remove extra spaces with TRIM()
    • remove nonprinting characters with CLEAN()
    • make sure both sides use the same data type

    For example, helper key formulas can be made more robust like this:

    =TRIM(A2)&"|"&B2

    and

    =TRIM(D2)&"|"&E2

    If the item numbers are stored as text in one table and as numbers in the other, convert them so both sides match. Excel guidance for lookup errors specifically calls out different data types and hidden spaces as common causes of #N/A or incorrect lookup results.

    If #REF! is appearing, that usually indicates the formula is referring to an invalid range or broken reference. Recheck that the lookup array and return array point to valid ranges of matching size.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

    1 person found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.