Given a string, find a corresponding substring on a list

[yodito]

Hello,

This is just a small extract of my scenario:
A1 Midas / B1 [NULL]
A2 Google / B2 [NULL]

A4 oogl / B4 Value1
A5 idas / B5 Value2

Is there a way to put an Excel formula on B1 & B2 that looks for the corresponding string on column A (i.e Midas) and looks through the list of substrings (A4 & A5) and if found populate the corresponding value of the substring on it? In this case, B1 would be populated with "Value2" and B2 with "Value1". If not a formula, what's the best way to accomplish this on VBA? I can only think of sucky loops that would take forever.

Any help is appreciated. Thank you!!

 

[John Davis]

Hello,

This is just a small extract of my scenario:
A1 Midas / B1 [NULL]
A2 Google / B2 [NULL]

A4 oogl / B4 Value1
A5 idas / B5 Value2

Is there a way to put an Excel formula on B1 & B2 that looks for the corresponding string on column A (i.e Midas) and looks through the list of substrings (A4 & A5) and if found populate the corresponding value of the substring on it? In this case, B1 would be populated with "Value2" and B2 with "Value1". If not a formula, what's the best way to accomplish this on VBA? I can only think of sucky loops that would take forever.

Any help is appreciated. Thank you!!

I think your sample maybe missing a letter. Take a look at VLOOKUP, it maybe what you are looking for.

 

[yodito]

Thank you John, I believe (correct me if I'm wrong) VLOOKUP will not work because I'm not trying to find a string that contains a substring, I'm doing the opposite which is, given a string, search through a list of substrings (A4 & A5) and pull the corresponding substring value. In the case above, for A2 (Google), the formula will go through the entire substring list and find "oogl" is the substring we are looking for, and then return "Value1".

 

[John Davis]

Thank you John, I believe (correct me if I'm wrong) VLOOKUP will not work because I'm not trying to find a string that contains a substring, I'm doing the opposite which is, given a string, search through a list of substrings (A4 & A5) and pull the corresponding substring value. In the case above, for A2 (Google), the formula will go through the entire substring list and find "oogl" is the substring we are looking for, and then return "Value1".

Ok, then. Those are not typos, and VLOOKUP from my knowledge will not work. I can't offer anything else at this time. Sorry.

 

[GTO]

Hi there,

Maybe just me, but this would seem difficult w/o some sort of rule we can count on. In your example, the substring is everything past teh first letter. If that is always the case, then we know what to tell it to look for. But if there is nothng we can count on (logic wise) as to tell it what part of the string we are to look for...