Forum Discussion
connerh99
Apr 07, 2021Copper Contributor
Search through a string of text for a specific value and relate it to a horizontal value
I am trying to create a script that allows me to type in the unique ID in a cell and have it spit out the value of the product name in the horizontal row corresponding to the unique ID. The main catc...
MindreVetande
Apr 07, 2021Iron Contributor
I can hardly see your image but i think you have to reverse the search if you want to use wildcard (look for the value from A1:A120 in G3). This should give you the first match
=MATCH(1,isnumber(search("*"&A2:A130&"*",G3))*(A2:A130<>""),0)
"*"&A2:A120&"*" will create a wildcard string (*23*) and look in the text value in G3
"'"&G3 ('1234).
Match will return the position of the first value that gives a "1"
Alternative
=MATCH(1,XMATCH("*"&A2:A120&"*","'"&G3,2),0)
Add index.
=INDEX(B2:B120,MATCH(1,isnumber(search("*"&A2:A130&"*",G3))*(A2:A130<>""),0))
As for the rest of the code (the row(xxx)) im lost
You might want to add some kind of count. just to make sure you get uniqe hits
sum(isnumber(search("*"&A2:A130&"*",G3))*(A2:A130<>""))