Using If statements with text

[jpm021990]

Hi,

Im trying to check column A for specific text and if it finds it put a value in column 2, i have multiple if statements and they work, the problem is if i apply the function to the whole row, it pasts "false" in column 2 and it ends up freezing excel because it does it thousands of times

is there any way to stop it?

this is my function

=IF(ISNUMBER(SEARCH("BF-O",A:A)),"3",IF(ISNUMBER(SEARCH("BF-W",A:A)),"1.9",IF(ISNUMBER(SEARCH("BF-R",A:A)),"2.4")))


thanks

 

[Norie]

Why not use COUNTIF?

 

[Scott Huish]

Couple of questions.

Is BF-O the entire contents of the cell or will there be other text with it?
What do you mean the entire row, your formula only references a column.
Will there only be 1 of either of those 3 values in the column or can there be any combination of them?

 

[jpm021990]

Couple of questions.

Is BF-O the entire contents of the cell or will there be other text with it?
What do you mean the entire row, your formula only references a column.
Will there only be 1 of either of those 3 values in the column or can there be any combination of them?

im sorry i meant column, and it will either be BF-O, BF-R, BF-W or nothing in column A, my problem is that for the empty spots, its putting "false" in column B and its freezing the program because its putting so many "false"s

there will only be 1 value in each cell

 

[jpm021990]

Why not use COUNTIF?

COUNTIF wont work for me because if I have BF-W , i want 1.9 in the next column

 

[Norie]

Why won't it work?

=IF(COUNTIF(A:A,"BF-O")>0,"3",IF(COUNTIF(A:A,"BF-W")>0,"1.9",IF(COUNTIF(A:A,"BF-R")>0,"2.4","")))

 

[jpm021990]

Why won't it work?

=IF(COUNTIF(A:A,"BF-O")>0,"3",IF(COUNTIF(A:A,"BF-W")>0,"1.9",IF(COUNTIF(A:A,"BF-R")>0,"2.4","")))

I just tested it, its putting 3 for all of my 3 variables

 

[Norie]

So would your original formula.

If 'BF-O' was found anywhere in column A that formula would return 3 and the rest of the formula would be ignored.

Where are you actually putting the formula?

Are you trying to check the entire column?

Or are you trying to check what's in column A on the same row as that you are putting the formula in?

 

[jpm021990]

Ok, In column A, I either have "BF-W" , "BF-O" , "BF-R" , or nothing

In column B, im pasting the formula

I want the formula to give me a 1.9 next to BF-W , a 3.0 next to BF-O or a 2.4 next to BF-R and nothing if theres nothing in column A

 

[jpm021990]

the problem im having with

=IF(COUNTIF(A:A,"BF-O")>0,"3",IF(COUNTIF(A:A,"BF-W")>0,"1.9",IF(COUNTIF(A:A,"BF-R")>0,"2.4","")))

is that it gives me 3 for everything, even if theres nothing on that row