Finding a value in one of two columns

[S Shipley]

I have a spreadsheet where column A contains 4 character department codes
i.e. A123 and column B contains multiple 4 character department codes
separated by commas. I want to find the value A123 in either column A or
column B and if found put a Yes in column C and if not found then put a No in
column C for that row. I have tried several different functions and
combinations of functions but I can't seem to get reliable results.

 

[Rick Rothstein \(MVP - VB\)]

Put this formula in C1 and then copy down as far as you like...

=IF(A1&B1="","",IF(ISERROR(SEARCH("A123",A1&B1)),"No","Yes"))

Rick

 

[Rick Rothstein \(MVP - VB\)]

Well, technically, this formula could produce a false "Yes" (depending on
whether two department codes can be concatenated so that an embedded
department code can be found inside the concatenated text). This formula
should work no matter what your department codes look like...

=IF(A1&CHAR(1)&B1=CHAR(1),"",IF(ISERROR(SEARCH("A123",A1&CHAR(1)&B1)),"No","Yes"))

Rick

 

[S Shipley]

Thanks Rick,

This solved the problem - I should have written the post earlier and I
wouldn't have pulled so much hair out.

 

[Rick Rothstein \(MVP - VB\)]

Thanks Rick,

You are welcome.

This solved the problem - I should have written the post earlier
and I wouldn't have pulled so much hair out.

You wrote that to a bald man

https://mvp.support.microsoft.com/profile/Rick.Rothstein)

so you won't get too much sympathy for your missing hair from me.<bg>

Rick