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 return a numeric value in column 3. I’ve no idea how to do it. I’m familiar with v/h lookup functions but in this case the codes I’m looking up are just part of a text string. I can’t extract the codes to then do a lookup as they are variable length and format. Any ideas? The logic is to lookup a code and if the code appears in the string to return the value in vol 3
Thanks
Thanks
Use wild card
=INDEX(Your3rdColumnValues MATCH("*"&YourCode&"*",YourColumnWithCodeMixed,0),1)
5 Replies
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. 👏😁
- Please provide a sample file with the desired output.
