Issue with Nested If_need help

[mdhar]

Hi,

My issue is little weird. I am using the following formula in my excel. Each of these two if blocks, separately, works fine. However, when I combine then, excel only works on the first block. In this case, if a text has "$", it will work perfectly, but in other case it will throw '#value' error. Please help.

=IF(FIND("$",U16,1)>0, V16, IF(FIND("€",U16,1)>0, VLOOKUP("EUR",Sheet2!$B$2:$C$14,2), "NA"))

 

[Jonmo1]

If U16 does NOT contain a $ symbol, then FIND("$",U16,1) returns the #Value! error.
contrary to popular beliefs, #Value!>0 DOES NOT result in FALSE.
As soon as the if function encounters an error like #Value! or #N/A, then the overall result of the IF is that error. Regardless of other results in nested functions.

You have to trap that error with ISERROR or ISNUMBER

Try
=IF(ISNUMBER(FIND("$",U16,1)), V16, IF(ISNUMBER(FIND("€",U16,1)), VLOOKUP("EUR",Sheet2!$B$2:$C$14,2), "NA"))

 

[mdhar]

Thank you so much. It works

 

[Jonmo1]

You're welcome.

 

[mdhar]

Hi,

I do have another issue now- very much related to the first one. I am using this formula =IF(ISNUMBER(FIND("$",U59,1)),V59,IF(ISNUMBER(FIND("€",U59,1)),VLOOKUP("EUR",Sheet2!$B$2:$C$14,2)*V59,IF(ISNUMBER(FIND("Renmibi",U59,1)),VLOOKUP("CHY",Sheet2!$B$2:$C$14,2)*V59,IF(ISNUMBER(FIND("RUB",U59,1)),VLOOKUP("RUB",Sheet2!$B$2:$C$14,2)*V59,"NA"))))

the vlookup is referring to one table in another sheet. The formula calculates correctly if it finds the currency (EUR, RUB etc. ) in sheet 2. However,if any such currency is missing, it still calculates using the available conversion rate for any other incorrect currency symbol. Can anyone help? I want the formula to calculate if the currency is there in vlookup table. However, if the currency is not there, the result should be "NA". Thanks a lot.