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

 

[Scott Huish]

I'm guessing the latter, if you are checking on a row by row basis:
This formula is for XL2007 and higher:
=IFERROR(VLOOKUP(A1,{"BF-O",3;"BF-W",1.9;"BF-R",2.4},2,0),"")

 

[jpm021990]

=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")))

works correctly, it gives me the number i need, but it pastes "false" if the row is empty and since im pasting this to all of column B, its giving me thousands of "falses"

 

[Norie]

You don't an entire column in the formula.

Put this in B2 and copy down.

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

 

[István Hirsch]

Give this a try (put it in B1 and copy down):

=COUNTIF(A1,"*BF-O*")*3+COUNTIF(A1,"*BF-W*")*1.9+COUNTIF(A1,"*BF-R*")*2.4