Searching a string within a string

[himanshu83]

I have a task at hand that I couldn't figure out how to do... There are three columns and two parts to the task

1. Query all the strings in column B into cell "A2" and find if anyone matches it.
2. If there is a match then return the corresponding value from column "C"

e.g. Cell A4 has the string in cell b12 and the outcome should be C12.


There is no defined starting poing and marker, so I can't use Find, Left or midle function.

I know the second part could be done with Vlookup as long as part 1 works. Unfortunately, thats where I am stuck.

I'd very much appreciate if someone can help me with it.

Thanks,
H

[Andrew-R]

The formula to do what you're asking for (check to see if a cell in column A contains any of the values in column B and, if it does, return the value from the same row in column C) is:

=IFERROR(INDEX($C$2:$C$824,MATCH(TRUE,INDEX(ISNUMBER(FIND($B$2:$B$824,A2)),0),0)),"")

But that doesn't match your example - how does the value of A4 (07OEDOC220) contain the string from B12 (ABSC)?

[Ace_XL]

maybe ..

Please Login or Register  to view this content.