Finding a value in one of two columns

  • Thread starter Thread starter S Shipley
  • Start date Start date
S

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.
 
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
 
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
 
Thanks Rick,

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