Hi Reader


Simple Excel formula, How to set the formula ?
I try =IF(SEARCH("K",C2,1) 0,1000,IF(SEARCH("M",C2,1)1,1000^2,1))
not work

Check B in 536.0B return 1
Check k in 14.1k return 1000
Check M in 41.1B return 1000000
Check G in 1.2G return 1000000000

The problem is that SEARCH does not return 0 when it finds nothing, it
returns #VALUE. Try

=IF(ISNUMBER(SEARCH("K",C2,1)),1000,IF(ISNUMBER(SE ARCH("M",C2,1)),1000^2,1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"moonhk" wrote in message
...
Hi Reader


Simple Excel formula, How to set the formula ?
I try =IF(SEARCH("K",C2,1) 0,1000,IF(SEARCH("M",C2,1)1,1000^2,1))
not work

Check B in 536.0B return 1
Check k in 14.1k return 1000
Check M in 41.1B return 1000000
Check G in 1.2G return 1000000000

Hi,

You can use a Lookup() function :

=LOOKUP(RIGHT(A1,1),{"B","G","K","M"},{1,100000000 0,1000,1000000})

HTH