Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 37
Default IF formula with multiple data range in cell

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 :)
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 563
Default IF formula with multiple data range in cell

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" wrote in message
...
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 :)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup data in multiple cell range DejanFlo Excel Worksheet Functions 3 September 2nd 09 02:28 PM
Most efficient formula/combining multiple data cell ranges/seperat Tiff New Users to Excel 1 August 2nd 06 04:56 PM
Multiple Range Formula Earl Excel Worksheet Functions 2 March 3rd 06 08:42 PM
Excel - Can i get a top 10 across a range of data in multiple wor. quality831 Excel Discussion (Misc queries) 1 April 22nd 05 02:42 PM
INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell Tourcat Excel Worksheet Functions 1 February 8th 05 07:26 PM


All times are GMT +1. The time now is 03:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"