IF(OR(ISTEXT

[JoC81]

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 · Accepted solution]

@JoC81 

=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.

[#3]

@OliverScheurich

This has worked perfectly! Thanks do much!

Jo