Forum Discussion
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 catch here is that sometimes the input number is a lot longer than the unique ID. The only value I need as the output is the product name. The input number will still have a corresponding Unique ID, just with a longer number sequence in the front and backside of it. Basically, I need to be able to search through the unique ID list to see if any one of those values is integrated into the longer input value. For example, if my input number is "1234," the code will have to know it is referring to unique ID "23," since "23" is in between "1234." I have a screenshot of the code I have right now. Anything helps. Thanks.
3 Replies
- For one thing, your formula has "IANUMBER" instead of ISNUMBER and is why you are getting #NAME.
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<>""))
Sorry, I can recognize nothin in your screenshot, perhaps you may submit sample file with the same information.
