Search for multiple substrings and return different values
Solved/Closed
jotied
jotied
- Posts
- 3
- Registration date
- Wednesday September 9, 2015
- Status
- Member
- Last seen
- September 9, 2015
jotied
- Posts
- 3
- Registration date
- Wednesday September 9, 2015
- Status
- Member
- Last seen
- September 9, 2015
Related:
- Search for multiple substrings and return different values
- If cell contains multiple text then return value ✓ - Forum - Excel
- Search inside an array and return a row? ✓ - Forum - Office Software
- Compare multiple cells and return other value ✓ - Forum - Excel
- Search word from list, match it copy values next to it ✓ - Forum - Excel
- Searching a column for a list of values ✓ - Forum - Office Software
1 reply
Mazzaropi
Sep 9, 2015 at 08:48 PM
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
Sep 9, 2015 at 08:48 PM
jotied, Good evening.
Try to use:
=IF(ISNUMBER(SEARCH("month",I2)),"Montly",IF(ISNUMBER(SEARCH("year",I2)),"Yearly","Others"))
Is that what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Try to use:
=IF(ISNUMBER(SEARCH("month",I2)),"Montly",IF(ISNUMBER(SEARCH("year",I2)),"Yearly","Others"))
Is that what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Sep 9, 2015 at 09:24 PM
For anyone else out there, I also tweaked the formula a bit to get another value. I have two columns, one with "payment amount- column I" and another with "payment frequency- column V". I was ultimately trying to find the average revenue per user per month. To get that, I just had the formula print the value from V column in case the payment frequency was monthly. If payment frequency was yearly, I had the formula divide the value in V column by 12. See below for example and I hope it helps someone else out.
=IF(ISNUMBER(SEARCH("month",I2)),V2,IF(ISNUMBER(SEARCH("year",I2)),V2/12,"Other"))
p.s. I'm starting the formulas in row 2, thus using 2 on all columns (e.g. I2, V2).