Formula not working - Return value in cell if another cell contains text from a list

Question

I am trying to get a formula that would evaluate the cells in the column 'data' to see if they contain any of the words contained in the column 'List', then if true return the word "Groceries" or otherwise leave the cell blank in the 'Output' column. The formula I am using seems to work for the top cell only.&nbsp;=IF(OR(COUNTIF(A2,"*"&amp;$D$2:$D$4&amp;"*")), "Groceries", "Nil")&nbsp;&nbsp;&nbsp;Can anyone help?&nbsp;DataOutput&nbsp;ListALDI STORES - NORTH RY&nbsp;&nbsp; NORTH RYDE AUGroceries&nbsp;AldiBrewscape Cafe ForestvillRHODES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AUNil&nbsp;ColesCOLES 5791&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MACQUARIE PK AUNil&nbsp;WoolworthsCarnivalFunPass&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CARNEGIE&nbsp;&nbsp;&nbsp;&nbsp; AUNil&nbsp;&nbsp;WOOLWORTHS/TOP RYDE CITY RYDE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AUNil&nbsp;&nbsp;&nbsp;

oliverscheurich · Accepted Answer

MattSmith1375&nbsp;Your formula works in my Excel 2013 when it's entered as an arrayformula with ctrl+shift+enter. If we don't work with Office 365 or Excel for the web we have to enter arrayformulas with ctrl+shift+enter.

Forum Discussion

Formula not working - Return value in cell if another cell contains text from a list

2 Replies

Resources

Data	Output	List
ALDI STORES - NORTH RY NORTH RYDE AU	Groceries	Aldi
Brewscape Cafe ForestvillRHODES AU	Nil	Coles
COLES 5791 MACQUARIE PK AU	Nil	Woolworths
CarnivalFunPass CARNEGIE AU	Nil
WOOLWORTHS/TOP RYDE CITY RYDE AU	Nil