What about multiple criteria in same formula

Closed
Report
-
 bradabcd -
Hello,
the formula [=IF(OR(ISERROR(SEARCH("*apple*",a1,1)),"","A ")] works great, but what if I want to use multiple criteria with wild characters.
EG: *apple* = A
*orange* = B
*grapes* = C
if any thing other than apple, orange or grapes, it should show 'D'

Hope you would respond.

Thanks in advance

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
try this
the data is like this in A1 to A4

apple frut
orange fruit
grape fruit
banana fruit
in B1 copy paste this formula

=IF(NOT(ISERROR(SEARCH("apple",A1,1))),"A",IF(NOT(ISERROR(SEARCH("orange",A1,1))),"B",IF(NOT(ISERROR(SEARCH("grape",A1,1))),"c","d")))

copy B1 down.

you have only seven ifs in many versions of excel
Hey venkat, that was really cool. Thanks dude.
Hi Venkat,
It would of great help if you can solve this for me.
September
1st preference aa bb cc dd ee
alpha 4 5 0 5 2
beta 2 3 0 3 1
gama 1 4 8 9 6
delta 0 1 9 3 7

2nd preference

alpha 3 6 7 8 1
beta 1 0 2 9 4
gama 28 9 15 2 6
delta 16 19 3 8 5
I have this data month wise

This is a separate sheet

bb 1st preference 2nd preference
september alpha ? ?
beta ? ?
gama ? ?
delta ? ?

I need to have "?" values
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
I am not clear about your requirements. It wold be helpful to see the sheet(s) even if it with fictitious data for security reasons and your required result sheet. If there is provision in the forum for posting the sheets please do so otherwise you send a message to me through the forum Administrator, if this is allowed.
Hi Venkat,
I understand that I was not able to give a clear description of the issue. I would like to send u the file to check it, whereas I don't see a way to do so. If you can provide me any Email address where I can send u the attachment, it would be of great help. I am very poor in excel and would appreciate any help from your side.

Thanks & Regards

Bradabcd