Searching for a value within a string across two columns.

[szaqyt]

Hi all,
I’ve been battling with this for few days and wondered if anyone could help.


I have two columns ‘Code A’ and ‘Code B’ with some of the values on their own and some ‘combined’ with “/”. What I want (in column D) is to look at the code in column A and, if there is a match for it in column B (either on its own or within combined string), print it in column D (so in effect, I’m looking for duplicates).


I’d rather try formulas first and not VBA if at all possible.

Any help much appreciated. Thanks!

[jewelsharma]

try this (e.g. Enter this in cell D3):

Please Login or Register  to view this content.

[szaqyt]

Hi jewel, thanks for the suggestion! This however, will only look at the same line (A3 -> B3) rather than a range (A3 -> B2:B8), and will only return value if the code from column A (4-letter one) is somewhere within column B.
Ideally I want to look for the code around "/" symbol (4-letter code in this example) from column A (e.g. look for 6666 from A6), find it "somewhere" in column B (e.g. B4) and print it in column D.
Sorry for the confusion and poor explanation.

[jewelsharma]

If that's the case, so long as the value in Column A is just 4 digits (i.e. just a single value like "1111" and not a composite value like "3333/8888"); try the following:

Please Login or Register  to view this content.

For composite ones, my guess is that such can be handled via VBA only.

[AlKey]

Try this one in D2

=INDEX($A$2:$A$20,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($B$2:$B$20,B2)),,),))

[jewelsharma]

Hi AlKey,
Just an observation, the Formula works perfectly when the value in 'Code A' is found anywhere in the 'Code B' range. However, the Formula evaluates to the corresponding value in Column A, even if that value does not exist in any cell of 'Code' B. I believe this is not what the OP wants. .... or am I missing something!

Regards,
Jewel

[AlKey]

@jewelsharma,

There is an Outcome provided by OP in column D. From what I see, if there is a match it returns match and if not it returns value from A.

[jewelsharma]

Hi AlKey,
I felt the OP desired to have cell in column D populated only if it matches with the range in Column B. See the last entry on OPs attachment, for cell A8 = 1010, the Outcome in cell D8 = "N/A". Else, we'll always be returning the value from Column A for all cells; irrespective of whether or not their is any match with the range in Column B. What are your thoughts?

Regards,
Jewel

[AlKey]

My thoughts? I have no thoughts anymore Or just trying not to have any.

[szaqyt]

That's great! Thank you both.

AlKey thanks for your suggestion but jewelsharma is right - I wanted to populate column D only if a match was found in column B.
I played around with jewelsharma's formula and added an extra condition which gives me a required result but it's not very flexible (it looks to the right of "/" in column A):


=IF(AND(ISERROR(MATCH("*"&A2&"*",$B$2:$B$8,0)), ISERROR(MATCH(A2, $B$2:$B$8,0)),ISERROR(MATCH(RIGHT(A2,FIND("/",A2)-1)+0,$B$2:$B$8,0))),"NO MATCH", A2)


It's not flexible, as I can't think of a way to search for a value within a range of column B values which contain "/", e.g. in the above formula, if I look to the LEFT rather than RIGHT I will not find A6 (value in B4) or A7 (value in B4). Have you guys any suggestions?

[AlKey]

So, you effectively misled me