Reference match when data doesn't match exactly

[nilani]

Hi,

I am trying to bring together data from 2 different systems. Unfortunately the unique code in the 2 systems don't always match exactly. I have put together an example on the attached.
Working file tab has one set of data, from one system and system download tab is from a different system. I want to be able to look up the value in column B of working file, in Column B of system download tab. If the text exists then I want it to return the reference from system download into working file.

For example :

Working file has (B3) ABC000
System Download has ABC000TB (B2). So this has 2 additional characters.

In working file D3 - I want it to return ABC000TB.

Unfortunately the number of characters can vary. Also, if there are more than one option in System Download then I want it to give me "CHECK" as a warning that there's more than one option. e.g. TJA009 has 2 options as it could have "-Box" or "D".

Excel Version : Microsoft 365 Version 2410

[AliGW]

Administrative Note:

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The four most recent Excel products are Excel 2019, Excel 2021, Excel 2024 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[AliGW]

You could try this:

=INDEX('System download'!$B$2:$B$12,MATCH(LEFT(B3,5),LEFT('System download'!$B$2:$B$12,5),0))

[nilani]

Unfortunately the number of characters can vary from line to line so the Match function hasn't worked for me, nor has XLookup , also where there could be 2 solutions, and here I need it to highlight this so that it can be reviewed

[AliGW]

XLOOKUP is not in 2010 - please see post #2 and update your profile.

It works in your sample file. I can't provide a solution for data that doesn't really represent the real data - can you please provide something more realistic?

[nilani]

Hi Ali,
I have added in the version of Excel used in the post.
I can't share the actual data as it's confidential information. I have a feeling it's not possible to do this due to all the variations in the data, but thought someone here might have some thoughts.

[harprik]

Hi, you could use a partial match vlookup. I am using different language excel so please check if the syntax is the same for ENG excel (the " " signs are sometimes used as ' ', but it should look like this:

Please Login or Register  to view this content.

It checks first if there is one or more partial matches and if it is only one, than returns the possible match.

[nilani]

Thank you Harpik, this is working for the first line, but when I copy for the other lines it's not working. I'm getting NA's

I think this is because on the other lines, I have less characters in the lookup not more

[AliGW]

I have added in the version of Excel used in the post.

That's not what I asked you to do - your profile still says 2010. Please get this updated ASAP. Thanks.

I can't share the actual data as it's confidential information.

I didn't ask you to share the real data. I asked for a more realistic sample set. Provide a set that shows that my formula is not working.

[RhiSJ]

How about this formula, please try this

Please Login or Register  to view this content.

[JohnTopley]

Building on Harpik's formula...

=IFERROR(IF(COUNTIF('System download'!$B$2:$B$12,LEFT('Working file'!$B19,6) &"*")>1,"Check",VLOOKUP(LEFT($B19,6) &"*",'System download'!$B$2:$B$12,1,0)),"")

[nilani]

Thank you so much RhiSJ, this is getting me further along , where it's not working is where I have lots of references beginning with M but M is actually a reference in it's own right. So with the example data set, if in line 22 I have just "t", that's where it falls over

[AliGW]

@Nilani

You are ignoring my requests. I have asked you to update your forum profile twice now. Please do so.

No further assistance is to be offered until the forum profile has been updated.

I also asked for a more detailed set of sample data for testing purposes. Please do not ignore my requests. Thank you.

[RhiSJ]

I know it, but you must consider that less charecters substring cause more problems,
so you should wirte proper brief strings.
If you are using Excel 2021 or newer version, FILTER function would be useful.

[nilani]

Hi John, this also works except for where I have a 1 to 2 characters but then have other references that have the same. So these are coming up as Check but actually the M is a reference in it's own right and then I have other's that also begin with M. I may just have to input those manually maybe. This goes a long way to getting me closer to pulling this together.

[nilani]

Don't worry Ali, others have been able to help me with my query. I'm not going to change my profile Excel as I use 2010 at home and mostly that's what I have queries on. I'm doing something for someone else at present so this is a one off. Thanks

[AliGW]

Fair enough, but next time explain this at the outset (opening post) instead of ignoring a Moderator.

I also helped: my formula in post #3 works as well, if not better, than anyone else's on the sample data. Did you actually try it?

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[AliGW]

In 365, try this copied down:

=TEXTJOIN(", ",,FILTER('System download'!$B$2:$B$12,LEFT('System download'!$B$2:$B$12,6)=LEFT(B3,6),""))

[nilani]

Thanks Ali, this is working also, I don't know this filter function, but it's giving me a list which I can also work with. Thank you

[AliGW]

Yes, that's what the TEXTJOIN bit does. I guessed that a list instead of a note to check might be of more use to you.

[RhiSJ]

Please try this formula, at first it search exact value, if not found search wild card again.

Please Login or Register  to view this content.