IN VLOOKUP ISSUE

Iron Contributor

In Vlookup issue.

Serial No.                             Serial No.

 

D-155846437-6-1               D-155846437-6-1 

 

Both are the same but the lookup is not working properly.

 

Could you please guide me to solve this issue?

 

6 Replies

@ajmal pottekattil yoousuf 

In file they are different:

7-6-1 and

76-1

at the end

image.png

In this case is it possible to look up the value?
Both are instruments serial no.
The user is typing like this in software different ways.
Is it possible to solve this issue?

@ajmal pottekattil yoousuf

 

In the cells Sheet1!$B$2:$B$82283 alone there are already over 10000 entries that are not unique. You could now eliminate the "-" characters with Substitute for XLOOKUP(), but this makes the problem even bigger (over 400 additional duplicate entries). In my opinion, the column is not suitable for an XLOOKUP(). You get wrong results.

Your XLOOKUP() results already return 1044 results that are potentially incorrect because the entry in column A in the original list is not unique.

 

Could you please help me to find out which entries are wrong?
Is it possible to look up the values?
Could you please guide me to solve this issue?

@ajmal pottekattil yoousuf 

I tried Fuzzy Join on both columns (Sheet2). Don't think that's help, just to illustrate there are a lot of differences.

@ajmal pottekattil yoousuf 

Please sort by column B in Sheet1. You can also use conditional formatting to display the duplicate entries in the column.