Find exact match in string array

[Pojzon]

Hello,
Please see attachment. I'm trying to find exact letters in array of strings (few k rows to check) and return code and name of the found record.
So far, I've been trying to do it with this formula:

Please Login or Register  to view this content.

but when I'm looking for example for text: "7523M" in strings array I will get in return "3M STARTPAKET M 7523MF ERS?TTER 06782" and that is incorrect, I'm only interested specifically in 7523M not 7523MF.
Text in strings array isn't always at the same spot, it can be at the end, middle or start, so it's random.
How can I achieve that?

[Phuocam]

Try this

=XLOOKUP("*"&[@[To find]]&" *";TbSearch[Name];TbSearch[Name];;2)

[Pojzon]

Thank you for help, but this solution doesn't seem to work with every case that I have. For example:
Searching for 289A and there is text "3M ACETAT AUSTAUSCHSCHEIBEN 289A", but your formula shows error in this case or,
searching for 972 and your formula returns "FESTO FILTERSACK FUER CT 44 452972 3M 202616" when it shouldn't.

[sandy666]

with Power Query

is that what you want?

To find	Code	Name
INOX	17	* 046300 VIS INOX A2 4,8 X 38 (200P)
750	21	* SEUIL DINAC 3M REF 207500
5901NEW	19	**obsolete**4X48,5ML KUNSTSTOFF-REPARATURMATERIAL 05901NEW DOPPELKARTUSCHEN-SET
26034	10	3M 26034 PPS SERIES 2.0 ADAPTER,TYPE S-4
79	20	3M 734 SCHUURPAPIER 25ST P240 01979
289A	5	3M ACETAT AUSTAUSCHSCHEIBEN 289A
52025	11	3M CUBITRON II HOOKIT STREIFEN 737U 80X400MM 52025 P 400
65170	6	3M CUBITRON II Kapskiva T41, 76mm x 1mm x 8mm, PN65170
5535	3	3M FINISH-KONTROLSPRAY 55535
2010	18	3M HIGH PERFORMANCE CLOTH BLUE 02010HLBU
26811	13	3M HOCHLEISTUNGSDUESENKOPF HVLP 1,1 MM GELB 26811
26814	14	3M HOCHLEISTUNGSDUESENKOPF HVLP 1,4 MM ORANGE 26814
26818	15	3M HOCHLEISTUNGSDUESENKOPF HVLP 1,8 MM TRANSPARENT 26818
34240	9	3M Plastlagningslim 50 ml, 6 st/fp, 34240A
1639	7	3M PPS COLOR CORRECTIVE BULB 16399
2890S	4	3M PREMIUM FULL VISION GLASSES 2890SA
64698	16	3M SCOTCH BRITE VLIESBAND A CRS 13X457MM 64698
7523L	2	3M STARTPAKET L A2/P3 (ers 06783) 7523LF
7523M	1	3M STARTPAKET M 7523MF ERSÄTTER 06782
8878	12	3M STEINSCHLAGSCHUTZ WEIS.8878
972	8	FESTO FILTERSACK FUER CT 44 452972 3M 202616
RET	22	LUNETTES SECURET FIT 400 GRISE VERSION SOLAIRE

[Pojzon]

Thank you for help, but not exactly. As you can see in your examples to find 750 returned name "* SEUIL DINAC 3M REF 207500". I want to see only exact matches, if this name would be "* SEUIL DINAC 3M REF 750" then that would be correct.
I guess it needs to somehow look for spaces between words and if it's at the end or beginning of string. No idea if that is possible to do with excel?

[sandy666]

maybe

To find	Code	Name
INOX	17	* 046300 VIS INOX A2 4,8 X 38 (200P)
26034	10	3M 26034 PPS SERIES 2.0 ADAPTER,TYPE S-4
52025	11	3M CUBITRON II HOOKIT STREIFEN 737U 80X400MM 52025 P 400

[Pojzon]

That is a start that looks promising. I've checked your PowerQuery steps and Text.contains should be also flagging cases like:
Attachment 820756
Right? Why isn't it?

[sandy666]

your attachment is inappropriate added

[Pojzon]

Oh sorry, perhaps now:
Ok, I can see that you've tried adding spaces before and after the word to find, but as you can see that also skips correct records to find.

[sandy666]

I think I don't understand your logic in the picture
could you detailed explain what you mean?

edit:
I didn't tried but I added spaces

post example with proper expected result (excel file not a picture)

[Pojzon]

Sorry, perhaps I've poorly explained it.
In the first post, I've attached file with records highlighted in colors: red incorrectly matched, green correct.
Your PowerQuery returned only 3 matches, while it should have 9 (well maybe 8 because of the search for 8878 while text "3M STEINSCHLAGSCHUTZ WEIS.8878" contains word WEIS.8878, this one might be tough to match since there is no space before number).
What I need is to find exactly the same code from column "To find" in array of strings "Name" and then return corresponding "Code". It can't have any additional numbers or letters like 7523M shouldn't be returning record with 7523MF in it.
I'm not sure if that's possible to do with excel, since that code to find can be at the beginning of string, in the middle or at an end, hard to predict all the cases, it's just mixed in somewhere in the text and I'm trying to find this exact word.

[sandy666]

see this

To find	Code	Name
INOX	17	* 046300 VIS INOX A2 4,8 X 38 (200P)
26034	10	3M 26034 PPS SERIES 2 0 ADAPTER,TYPE S-4
289A	5	3M ACETAT AUSTAUSCHSCHEIBEN 289A
52025	11	3M CUBITRON II HOOKIT STREIFEN 737U 80X400MM 52025 P 400
26811	13	3M HOCHLEISTUNGSDUESENKOPF HVLP 1,1 MM GELB 26811
26814	14	3M HOCHLEISTUNGSDUESENKOPF HVLP 1,4 MM ORANGE 26814
26818	15	3M HOCHLEISTUNGSDUESENKOPF HVLP 1,8 MM TRANSPARENT 26818
64698	16	3M SCOTCH BRITE VLIESBAND A CRS 13X457MM 64698
8878	12	3M STEINSCHLAGSCHUTZ WEIS 8878

btw. your conditions are not correct because EXACT is EXACT not .26811 because GELB.26811 is the string which shouldn't be taken

[52779]

Hi,
as an alternative solution by formula
if you have TEXTSPLIT function

Formula - D2

Please Login or Register  to view this content.

[menem]

Maybe

array formula (followed by Ctrl-Shift-Enter)
=VLOOKUP("* "&[@[To find]]&" *"," "&TbSearch[Name]&" ",1,FALSE)

Ps. I'm use Excel 2016, so I didn't have XLOOKUP function.

Regards.

Note : it's need space as delimiter , so if you want to find ABC from ABC, or ABC. it won't.

[menem]

As I was mention above, the formula may need to changed to


=VLOOKUP("* "&[@[To find]]&" *"," "&SUBSTITUTE(TbSearch[Name],"."," . ")&" ",1,FALSE)

Regards.

[menem]

Oh, I've made some mistake.
the formula should be

=INDEX(TbSearch[Name],MATCH("* "&[@[To find]]&" *"," "&SUBSTITUTE(TbSearch[Name],"."," . ")&" ",0))

Regards.

[Pojzon]

Everything looks great! Thank you all very much for the help, you just made my life way easier.
Problem solved.