Prev: 2010 date in Excel 2000
Next: EXCEL FORMULA
From: stumped on 5 Jan 2010 12:51
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 :)
From: Bernard Liengme on 5 Jan 2010 13:03
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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"stumped" <stumped(a)discussions.microsoft.com> wrote in message
news:83DDADB3-E050-433D-AAFB-E9FCF808DE7B(a)microsoft.com...
> 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 :)

 | 
Pages: 1
Prev: 2010 date in Excel 2000
Next: EXCEL FORMULA