Find single value in string of text and return corresponding row

[Jrod2519]

Hello,

I am looking for a formula that will locate a text value within a array of text strings and then return the value on a corresponding row. Here is an example:

I have 3 columns, A,B,C

In column A I have 4000 rows of data and each row has a text string. These text strings are of varying length and are made up of different code combinations, and each code is sepreated by a column. So, A2 is 0359AGG669,5689HHG6526,33432V3,232VVD and A3 is 2231,456TTR4445 and A4 is 266215162,2656V3,0323V2,6262625454. As you can see these vary in length and content.

In column B I have IDs that correspond to the values in Column A. So, B2 = 1511233 and it corresponds to the text string in A2. So, each value in Column A has a corresponsing ID in Column B.

In Column C I have 439 rows of text strings. Each one of these are found within one of the text strings in Column A. So, in C2 I have 1245626 and this value is located in one of the text strings in column A.

I need the formula find each individual value in Column C and then tell me every instance where the value exists in column A, by returning the corresponding value in column B. So, there is one value in each row in column C and that value might exist in multiple text strings in Column A.

I am attaching an example.

[sktneer]

Try this array formula. Confirm it with Ctrl+Shift+Enter and drag it to the right and then down.

Please Login or Register  to view this content.

Please see the attached sheet.

[Jrod2519]

Thanks sktneer, I am closer now. This works great to locate a single instance where the value in Column C exists but it does not allow me to locate the value in other rows of Column A. Like in my example, TEST was in multiple rows. This pulls back the corresponding value when it locates it the first time but it cant locate it again.

Is it possible to modify this formula an create another column where the formula skips over the first time the value is found and finds the next row where the value is contained?

Thanks again for all the help!

[Jrod2519]

Thanks sktneer, I am closer now. This works great to locate a single instance where the value in Column C exists but it does not allow me to locate the value in other rows of Column A. Like in my example, TEST was in multiple rows. This pulls back the corresponding value when it locates it the first time but it cant locate it again.

Is it possible to modify this formula an create another column where the formula skips over the first time the value is found and finds the next row where the value is contained?

Thanks again for all the help!