IF formula with multiple data range in cell

  • Thread starter Thread starter stumped
  • Start date Start date
S

stumped

I'm trying to create an IF formula that returns data after searching a cell
that contains more than one data range. For ex:

Cell B3 contains GCVW-00001, GCVW-00002
I need the formula in C3 to return Yes if B3 contains GCVW-00001 and I need
the formula in D3 to return Yes if B3 contains GCVW-00002

I'm able to get the formula to return yes or no correctly if cell B3
contains either GCVW-00001 or GCVW-00002, but the formula won't work if it
contains both at the same time.

right now my basic formula looks like:
=IF(B3="GCVW-00001","Yes","No") or =IF(B3="GCVW-00002","Yes","No")

I'm thinking the problem is with the = portion. Is there a way to do Conains
instead of equals?

thanks :)
 
This will return Yes if B3 hold GCVW-00001 no matter what else is in the
cell
=IF(LEN(B3)<>LEN(SUBSTITUTE(B3,"GCVW-00001","")),"Yes" ,"No")
And if you can cope with simple TRUE or FALSE, this is simpler
=LEN(B3)<>LEN(SUBSTITUTE(B3,"GCVW-00001",""))

best wishes
 
Back
Top