String Search For Value Between Special Characters

Good evening, everyone!

I'm having a brain fart here tonight. So I have two lists: one with unique ID and Name while the other has an assorted variety of ways the name is given between special characters and spaces. Is there a search, index match formula that I can utilize to return the match?

sample_list.PNG

Try not upload an image but your sample excel file, click on "Go Advanced" button, next to "Post Quick Reply" button, then whenopen scroll down untuil find "Manage Attachments" text button to attach your file

Ok then, I re-type again your table, conside your table ListA on ranges A2:B4 and your ListB "SystemName" on Column D, put this on E3 and copied down:

=IFERROR(LOOKUP(1000,SEARCH(SUBSTITUTE($B$3:$B$4," ","*"),D3),$B$3:$B$4),LOOKUP(1000,SEARCH(LEFT($B$3:$B$4,FIND(" ",$B$3:$B$4&" ")-1),D3),B$3:B$4))

For ID, put this on F3 and copied down

=IFERROR(LOOKUP(1000,SEARCH(SUBSTITUTE($B$3:$B$4," ","*"),E3),A$3:A$4),LOOKUP(1000,SEARCH(LEFT($B$3:$B$4,FIND(" ",$B$3:$B$4&" ")-1),E3),A$3:A$4))

Sorry about that. I'll remember to do that next time, and thanks for the reply!


Quick question. So I'm understanding the substitute but what does the lookup of value of 1000 do? I'm assuming it's looking for the next closest value?

Originally Posted by huyza_0100

Sorry about that. I'll remember to do that next time, and thanks for the reply!


Quick question. So I'm understanding the substitute but what does the lookup of value of 1000 do? I'm assuming it's looking for the next closest value?

Is generally regarded as the biggest number and is often used to find the last number in a given range