Forum Discussion
Solved
How to lookup text in a string and return a value
I have circa 500 rows x 3 columns of data. The first column contains a text string with up to 20 words and a alphanumeric code. I want to lookup the codes from a table containing circa 600 codes and r...
Use wild card
=INDEX(Your3rdColumnValues MATCH("*"&YourCode&"*",YourColumnWithCodeMixed,0),1)
Use wild card
=INDEX(Your3rdColumnValues
MATCH("*"&YourCode&"*",YourColumnWithCodeMixed,0),1)
- Hi Juliano
Can I ask for your help with a related question? Some of the codes appear in multiple rows of the data table. As a consequence the formula will only return the value associated with the first instance of the code. Could I sum the instances or alternatively identify how many instances of the code there are so I could then find the duplicated codes and adjust them manually.
Thanks=COUNTIF("*"&YourCode&"*",YourColumnWithCodeMixed)
Thank you Juliano-Petrukio . I'll need to double check but it looks like you've got it in one. That's very kind of you. 👏😁
