If formula searching for text

Hello,
I have a spreadsheet that takes meal booking data and summarises it in another column.
The below formula is being used to show the text "Main 1" if it can't find certain words in column c or to leave the text as it if it does find them on our dinner bookings spreadsheet.
The problem is that blank cells (where no booking has been made) are also showing as main 1 and I need them to remain blank in this instance.
The formula was done from searching on forums before so may not be the simplest way of doing this.

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"Home Sandwiches","Margherita","Vegetarian","vegetable","quorn","cauliflower","butternut","ratatouille","Veggie","Cheese & Onion","Chickpea","Cheese & Tomato","Bean burger","Cheesy","Free","Jacket Potato"},C4)))))>0,C4,"Main 1")

Hope this makes sense and apologies if this is a simple thing and I'm just being daft! Many thanks

You could test for C4 being empty, like this:

=IF(C4="","",IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"Home Sandwiches","Margherita","Vegetarian","vegetable","quorn","cauliflower","butternut","ratatouille","Veggie","Cheese & Onion","Chickpea","Cheese & Tomato","Bean burger","Cheesy","Free","Jacket Potato"},C4)))))>0,C4,"Main 1") )

Hope this helps.

Pete

Try

=IF(C4="","",IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($H$2:$H$16,C4)))))>0,C4,"Main 1"))

H2:H16 contains your meal items and makes it easy to extend/reduce your list
Or

defiine a dynamic named range e.g

Name: Menu_Items

Refers to: =OFFSET(Sheet1!$H$1,,,COUNTA(Sheet1!$H$1:$H$100),1)


=IF(C4="","",IF(SUMPRODUCT(--(NOT(ISERR(SEARCH(Menu_Items,C4)))))>0,C4,"Main 1"))

no formula change required as you can add/remove items from the named range

Thanks very much for your help
Pete_UK that worked perfectly, thamks very much.
JohnTopley, I'd love to tidy it up more, the list gets bigger every time we change the menu. Unfortunately your formula didn't change it to main 1 if it couldn't find the words in the list?
I've attached (I hope) a spreadsheet with both formulae on there.

Use this ...

=IF($C2="","",IFERROR(INDEX($N$2:$N$50,MATCH($C2,$N$2:$N$50,0)),"Main1"))

SUMPRODUCT will give wrong results unless the you limit the range to N17 using Named range as per post #3

Perfect, thanks very much

Thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete

cell I2 formula , drag down

HTML Code: