Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup data in multiple cell range | Excel Worksheet Functions | |||
Most efficient formula/combining multiple data cell ranges/seperat | New Users to Excel | |||
Multiple Range Formula | Excel Worksheet Functions | |||
Excel - Can i get a top 10 across a range of data in multiple wor. | Excel Discussion (Misc queries) | |||
INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell | Excel Worksheet Functions |