Forum Discussion
Solved
Formula not working - Return value in cell if another cell contains text from a list
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.
=IF(OR(COUNTIF(A2,"*"&$D$2:$D$4&"*")), "Groceries", "Nil")
Can anyone help?
| 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 |
Unless I'm mistaken, to get exactly what you're asking for, you're going to need Power Query or a tool like that; sadly, I'm not experienced with that.
However, I am experienced with databases and database design, and I'm questioning why you have three separate sheets for the subordinate data here. One could suffice, I believe, recognizing that column B in those sheets is redundant with the name affixed to the tab. Just let that column do the differentiating that the tab is doing.
(An aside: we often create separate pages because that's how we'd do it on paper, but the reality is that such a layout actually (often) interferes with Excel's marvelous abilities to parse such distinct entities in a single database or table, just using a column to differentiate what was originally presented as separate tabs.)
When you do that--as I've done in the demo attached--then a simple FILTER function, nested in TRANSPOSE, gives you the desired result, albeit in three separate columns, G, H and I.
2 Replies
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.
- It worked! Thank you Oliver!
