[Solved] IF a field contains ref2 or ref2 or ref1+ref2

[ldkemp]

Hello,

I was hoping someone could help.

I am trying to create a function that will assign a value depending on if the word; dimond, gold or diamond and gold appear anywhere in a field.

I have tried =IF(FIND("diamond";D25);"Diamond";IF(FIND("gold";D25);"Gold";0))
But this only works for the first find function (diamond) and does not return any result for the second find function (gold)

Thank you for taking the time to read the post and if you can help I would be very greatful

Regards Liam

[acknak]

The FIND function produces an error if there is no match. Once any part of a formula results in an error, the whole formula result is an error.

You have to explicitly test for an error result using something like =IF(ISERR(FIND(...

[ldkemp]

I get error Err:508

[Villeroy]

I get error Err:508

Which is, according to the help files, a bracketing error. Resolve it. The function wizard is a useful tool (Ctrl+F2).

Code: Select all

=IF(isnumber(FIND("diamond";D25));"Diamond";IF(isnumber(FIND("gold";D25));"Gold";0))

I suggest without testing.

[ldkemp]

Thank you very much, that has worked a charm.

Regards Liam