Excel Question - IF Function

S

Slash

Registered User
Messages
329
If you have a range of cells in different currencies, how do you do this with the IF Function:

If the cell contains "USD", then return the value of a certain cell.

It's not IF(A1="USD"....), what i want is IF(A1 contains "USD".........), but how do I specify "contains".

Thanks.
 
=IF( SEARCH(string, cell#),do this if true, do this if false)

e.g.
=IF( SEARCH("USD", A1),1,0)
 
Thank you. That worked - almost.

Where the cell contained USD, it performed the "do this", but where the cell did not contain USD (e.g. GBP or EUR) it returned #VALUE!.

Where did i go wrong?
 
it sounds as if the value that you are setting to false ( the bit after the second comma) is invalid


Are you trying to muliple something by an empty cell, or a cell with text?
or you're trying to divide by zero
 
This is what is in the cell now:

=IF(SEARCH("USD",B11),D11*1.47,D11)

So, when "USD" is in cell B11, it multiplies D11 by 1.47, that's what i want it to do.

But, where "USD" does not appear, for example where EUR appears, it gives the error, instead of returning the value in cell D11.
 
sorry, i have mislead you.
You cannot use the value returned by the search function as a input to the if statement, because( I have just learned) that search returns the error value if the string is not found.

sorry about that.

looks like vlookup is the way to go.
 
search returns the error value if the string is not found.
Click to expand...
I'm pretty sure there's another function, very similar to 'SEARCH' that doesn't give an error value.
I can't remember what it is, I was doing this a couple of years ago. Try 'FIND'.

ISError() can be used to get rid of the #value thing

This kind of thing =IF(ISERROR(FIND("USD", G16, 1)), G16*1.47, G16)


++Try this
=IF(ISERROR(SEARCH("USD", B11)), D11, D11*1.47)
 
to simplify your spreadsheet why don't you separate the currencies from the numbers?
highlight the column you need to change and use the Data>Text to Columns function. select fixed width and add in a line break where you want to split out the USD etc. from the numbers.
 
webtax said:
to simplify your spreadsheet why don't you separate the currencies from the numbers?
Click to expand...


I did that and it works, but i feel sure there is some way to use the IF function. Will try Leghorn's suggestion tomorrow.

Thank you.
 
leghorn said:
++Try this
=IF(ISERROR(SEARCH("USD", B11)), D11, D11*1.47)
Click to expand...

That worked!!

i just need to figure out if i can insert additional arguments, like SEARCH("GBP"...), but pretty sure I can.

Thanks.
 
You must log in or register to reply here.
Menu
Log in
Register