Excel - If Function

Closed
Report
Posts
1
Registration date
Friday September 25, 2015
Status
Member
Last seen
September 25, 2015
-
Posts
1947
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 11, 2021
-
Hi
I'm trying to write a formula to produce an answer depending on whether the data in a cell contains any of the words I am trying to match.

Ie. I want the formula cell to read "Dining" if Cell F2 contains the word "Meal" or "Housing" if it contains Room, Rent, Housing or Lodging.

=IF(COUNT(SEARCH({"Meal"},F2318)),"Dining",""),IF(COUNT(SEARCH({"Room","Rent","Housing","Lodging"},F2318)),"Housing","")

Any assistance is greatly appreciated. Thank You

1 reply

Posts
1947
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 11, 2021
147
clchamberlain, Good afternoon.

I suggest to you a more manageable approach to its variables.
It is better and much easier to locate and change if necessary to maintain in your spreadsheet.

Create a small but efficient table.:

.....X.........
1..Meal
2..Room
3..Rent
4..Housing
5..Lodging

Try to use:

Matricial Formula

=IF(COUNT(SEARCH(X1:X5,F2)),"Dining",IF(COUNT(SEARCH(X1:X5,F2)),"Housing",""))

SHIFT + CTRL + ENTER after typed the formula

Is that what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão