BOM comparison - excel function

Question

BOM comparison - excel function

Dušica Garović 10

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

0 comments

6 answers

Your answer

Answer 1

Dear @Dušica Garović,

I hope you’re having a good day.

Thank you for the screenshots and the additional information.

Based on what I can see, your approach of creating a combined key such as: **Article & "|" & Quantity**is a reasonable way to compare the ERP BOM against the Master BOM.

However, from your screenshots, I notice that some values appear visually identical (for example, 2007.0689|1) but still return different results. In situations like this, the issue is often caused by one of the following:

Hidden spaces before or after the value.
Non-printable characters imported from the ERP system.
One side being stored as text while the other is stored as number.
Quantity values that look the same due to formatting but differ internally.

Could you please try the following checks?

Compare the lengths of the two keys

For two values that appear identical, use: **=LEN(A2)**and =LEN(B2)
If the lengths are different, there is likely an invisible character or extra space.

Clean the values before comparing

Try rebuilding the concatenated key using: =TRIM(CLEAN(ArticleCell)) & "|" & TRIM(CLEAN(QtyCell)) and then perform the lookup again.

Check whether Excel sees the values as equal

For a pair that appears identical, test: =A2=B2
If Excel returns FALSE, then there is some hidden difference in the underlying values.

Verify the lookup formula

I also notice a #REF! error in one of the screenshots. This can indicate that the lookup formula is referencing an invalid range or a deleted column. Could you please share the exact formula you are using for the comparison?

If possible, could you upload a small sample workbook containing a few ERP rows and Master rows that reproduce the issue? That would make it much easier to determine whether the problem is coming from the data itself or from the formula.

I hope this information helps point you in the right direction. If you run into any issues while trying the steps, or if something still doesn’t feel quite right, please don’t hesitate to reach out again. I’ll do my best to support you however I can.

Looking forward to hearing back from you with any updates or additional details.

Warm regards,

If the answer is helpful, please select "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in the forum documentation to enable email notifications if you want to receive the related email notification for this thread.

Dušica Garović 10 Reputation points

2026-08-13T08:01:34.6666667+00:00

![enter image description here](/api/attachments/5 c164a20-4e5a-44bd-951c-6688044f4648?platform=QnA)

I have checked if the two equal values gives the "true" feedbak and it works. Also, with the lens it's confirmed that the values has the same sum of characters. When I format as a 'general' some of the article numbers which ends on 0, 0 dissapear therefore I used ' before the number so I can keep the 0. But also, in these merged columns everything is set as a general.
Liora 775 Reputation points Independent Advisor

2026-08-13T15:42:23.5066667+00:00

Hi Dušica,

Thank you for your follow-up and for providing the additional details, screenshots, and formulas.

Based on the information you've shared so far, several of the checks we would normally use to identify the issue appear to be behaving as expected.

Because of that, I'm finding it difficult to further narrow down the cause from the screenshots alone. At the moment, I don't have enough evidence to conclude whether the issue is related to the formulas themselves, the underlying data, or a specific subset of records that may be behaving differently.

To help investigate this further, would it be possible for you to upload a sample workbook to OneDrive and share a link here?

If possible:

Remove or anonymize any confidential data.

Include a few examples that work correctly.

Include a few examples that you believe should match but are being reported as "nije pronađeno" or "ZA BRISANJE".

Set the sharing permissions to "Anyone with the link"

Having access to the actual workbook would make it much easier to inspect the data, formulas, cell formats, and ranges involved, and would likely help identify the root cause much faster than working from screenshots alone.

Thank you again for taking the time to provide such detailed information. I look forward to reviewing the sample file if you're able to share one.

Kind regards.
Liora 775 Reputation points Independent Advisor

2026-08-14T11:06:36.81+00:00

Hi Dušica,

I hope you're having a great day.

I'm following up to see if you've made any progress with this issue or if there are any updates you'd like to share.

Wishing you all the best, and I look forward to hearing from you.

Kind regards.

Answer 2

Dušica Garović 10

User's image

I have created additional columns which combine the data - article+quantity. All have format general.

0 comments

Answer 3

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:

Add a helper column in both tables that joins item and quantity.
- In the ERP table:
```
     =A2&"|"&B2
```
- In the Master table:
```
     =D2&"|"&E2
```

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")

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:

Answer 4

TERRENCE KHODANI MUDAU 0

that's really fine

Dušica Garović 10 Reputation points

2026-08-13T08:17:03.5033333+00:00

But it doesn't work.

Answer 5

TERRENCE KHODANI MUDAU 0

everything is under control

0 comments