Lookup match from other sheet

[Bstacks]

Hi all, could use some help with this one. I've been working on it with the help of copilot to no avail. I will do my best to explain what I am after.

Sheet1 has a column called "Number" (which can be alpha numeric)
Sheet2 has a product name called "Name" which can contain the number.

I need to do a lookup from the "Number" column and confirm if any product from Sheet2 contains that number. Then I also need to know how many products contain that number.

For example in the screenshot below I don't want a match of 7004 to 7004EG

 

[Kaper]

Are the numbers in the product always surrounded by hyphens? or coulkd be there also "7004EG-New-Flange" or "Fiiting-something-7004EG"

If there are always hyphens both before and after look for "-" & number & "-" instead of just number

PS. Saying that they are in column named Number or Name - are you saying that they in excel tables (with headers) - what are table names then? Or it is just Column X in one sheet and column Y on second sheet and the name is just mentioned in cell in first row

 

[Bstacks]

Hi Kaper,
Unfortunately not, the numbers are sometimes in the beginning, end, sometimes surrounded by hyphens, sometimes not.
The table name that contains the numbers I use as a reference is called "RFA_TXT_FILES" and I need to search for that information in a table called "ITMs"

 

[Kaper]

My idea would be to sort numbers list descending (this way 7004EG will be before 7004 and 7001-2 before 7001)
Then search names starting from the first number, but after finding a match, removing all fitting names from the list of items to be searched for next number.

I don't think it is easy to do with formulas, but probably VBA code would do the work reasonably quickly. Is a macro solution OK for you?

As for formulas - probably it could be implemented that way that first a list of all fitting numbers for given name is created, then sorting of such list descending, finally keep only the first one.

And as I understand the data is in excel tables.
To test if that's true - does formula:

=COUNTA(RFA_TXT_FILES[Number])

work and returns a number of numbers as well as

=COUNTA(ITMs[Name])

a number of names?