SOLVED

# IF(OR(ISTEXT

Copper Contributor

# IF(OR(ISTEXT

Hi,

I’m really struggling with a formula for the following scenario, please can someone help.

I have the following ‘Assignment Types’

Short Term Assignment

Commuter Arrangement (2015)

I need a formula to look at the text in a field and if it matches either of these Assignment Types it shows ‘Home’, if it doesn’t match, it shows ‘Host’.

I may need to add further Assignment Types later so I also need to know how to add in further text options to look for to show a result of Home.

@Sergei Baklan I’ve read a similar one that you helped with. Would appreciate any help!

Jo

2 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: IF(OR(ISTEXT

=IF(ISNUMBER(MATCH(D3,\$A\$1:\$A\$2,0)),"Home","Host")

This formula works in my sample file if i understand the question correctly. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

If you have e.g. 10 Assignment Types you can change \$A\$1:\$A\$2 to \$A\$1:\$A\$10.

# Re: IF(OR(ISTEXT

@OliverScheurich

This has worked perfectly! Thanks do much!

Jo
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: IF(OR(ISTEXT

=IF(ISNUMBER(MATCH(D3,\$A\$1:\$A\$2,0)),"Home","Host")

This formula works in my sample file if i understand the question correctly. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

If you have e.g. 10 Assignment Types you can change \$A\$1:\$A\$2 to \$A\$1:\$A\$10.