0

I am trying to create an IF statement that includes checking if a cell contains a text string from a list of text strings and I'm struggling with the correct way of doing it.

I have a 'Global Settings' page, with the values I want to search against (which are named ranges to make things cleaner).

enter image description here

Then in my Calcs sheet, I have column J which works correctly when the named range from the Global Settings is just one word and matches what's in column A.

But column K is the one I can't get to work, where I'm trying to check if the Post Type in column A is in the list of Page Types in the named range.

This is the formula I have:

=IF(B2>100, IF(A2=PageTypes, "Right Page Type", "Wrong Page Type"),"<100")

enter image description here

Logically I think I need to change the 'A2=PageTypes' part to somehow tell sheets to say if 'A2 is contained in the string of PageTypes' but I have no idea how I would do that.

Link to the spreadsheet here https://docs.google.com/spreadsheets/d/1TeaQ6oUbJDeKxUi8tvvCWXtw0oK9d5IVO60j1UbQCK8/edit?usp=sharing

player0
  • 124,011
  • 12
  • 67
  • 124
Jon Wright
  • 45
  • 4
  • https://webapps.stackexchange.com/questions/95649/google-sheets-formula-for-if-contains – 9bO3av5fw5 Oct 02 '22 at 14:42
  • Your question can be greatly improved if you add input table and expected output table to the question. Use [Markdown Tables](https://webapps.stackexchange.com/a/161855/) to show your data structure. If you share spreadsheets, your question maybe closed, as questions here must be [self](https://meta.stackoverflow.com/a/260455) [contained](https://meta.stackexchange.com/a/149892)-all the data needed to answer your question must be in the question itself. [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Oct 02 '22 at 15:21
  • 1
    Thanks, couldn't get the Regexmatch method to work, but that same thread took me down the route of using SEARCH which does work. My working formula now looks like this: `=IF(B2>100, IF(IFERROR(SEARCH(A2,PageTypes)> 0, 0), "Right Page Type", "Wrong Page Type"), "<100")` – Jon Wright Oct 02 '22 at 15:39
  • Hi, was your issue solved? If that's the case, consider posting an answer explaining what the solution was. – Iamblichus Oct 03 '22 at 08:14

1 Answers1

1

somehow tell sheets to say if 'A2 is contained in the string of PageTypes

=IF(B2>100, IF(REGEXMATCH(PageTypes, A2), 
 "Right Page Type", "Wrong Page Type"),"<100")

or:

=IF(B2>100, IF(IFERROR(SEARCH(A2,PageTypes)>0, 0), 
 "Right Page Type", "Wrong Page Type"), "<100")
player0
  • 124,011
  • 12
  • 67
  • 124