# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

MY QUESTION IS ABOUT: Fixing Formulas DESCRIPTION: Search thru Column H and Column I, and IF the cell CONTAINs STRING A, STRING B, OR STRING C, return VALUE 1 OR if cell CONTAINs String D, String E, OR STRING F, return VALUE 2, OR if cell CONTAINs String G, OR String H, return VALUE 3 Otherwise, return UNKNOWN.
Solved by M. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 15/04/2018 - 09:33
Hi
Excelchat Expert 15/04/2018 - 09:33
Hi
Excelchat Expert 15/04/2018 - 09:34
Welcome to Got it Pro
Excelchat Expert 15/04/2018 - 09:34
Excelchat Expert 15/04/2018 - 09:34
Are you there?
User 15/04/2018 - 09:34
Yes
Excelchat Expert 15/04/2018 - 09:35
User 15/04/2018 - 09:35
This is the formula I'm using
User 15/04/2018 - 09:35
=IFERROR(INDEX(\$B\$2:\$B\$9, IFERROR(MATCH(TRUE,ISNUMBER(MATCH("*"&\$A\$2:\$A\$9&"*",\$I:\$I,0)),0), MATCH(TRUE,ISNUMBER(MATCH("*"&\$A\$2:\$A\$9&"*",\$J:\$J,0)),0))),"UNKNOWN")
Excelchat Expert 15/04/2018 - 09:36
First let me understand your data and question
User 15/04/2018 - 09:36
Formula is in Column C
User 15/04/2018 - 09:37
As ou can see
Excelchat Expert 15/04/2018 - 09:37
I am not able to see any formula
User 15/04/2018 - 09:37
the data set in I and J for row 2 contains the key word alias
User 15/04/2018 - 09:37
so the output in C3 shouold be 'Storage Alias Issue'
Excelchat Expert 15/04/2018 - 09:37
Wait..
Excelchat Expert 15/04/2018 - 09:38
User 15/04/2018 - 09:38
Ok
Excelchat Expert 15/04/2018 - 09:38
There is a keyword in column A
User 15/04/2018 - 09:38
I have data in column I and J
User 15/04/2018 - 09:38
and I have a set of Keywords I need to search for in both of those columns
Excelchat Expert 15/04/2018 - 09:38
Short Description and description
Excelchat Expert 15/04/2018 - 09:38
ok fine.
User 15/04/2018 - 09:38
if those two columns contain any of the keywords in column A
Excelchat Expert 15/04/2018 - 09:38
ok.
User 15/04/2018 - 09:38
I want the column C to output the return value
User 15/04/2018 - 09:38
based on the keyword it finds in columns I and J
Excelchat Expert 15/04/2018 - 09:39
ok same Column A keyword should return
Excelchat Expert 15/04/2018 - 09:39
Else you need unknown.
Excelchat Expert 15/04/2018 - 09:39
Is it my understanding correct
Excelchat Expert 15/04/2018 - 09:39
??
User 15/04/2018 - 09:39
yes
User 15/04/2018 - 09:39
that is proper
Excelchat Expert 15/04/2018 - 09:40
ok.. Good. give me 5 minutes.
User 15/04/2018 - 09:40
Ok thanks
User 15/04/2018 - 09:40
=IFERROR(INDEX(\$B\$2:\$B\$9, IFERROR(MATCH(TRUE,ISNUMBER(MATCH("*"&\$A\$2:\$A\$9&"*",\$I:\$I,0)),0), MATCH(TRUE,ISNUMBER(MATCH("*"&\$A\$2:\$A\$9&"*",\$J:\$J,0)),0))),"UNKNOWN")
User 15/04/2018 - 09:40
This is what I'm using
User 15/04/2018 - 09:40
but it's not really giving the results I need properly
Excelchat Expert 15/04/2018 - 09:40
let me try give me 2 minutes
User 15/04/2018 - 09:40
ok
User 15/04/2018 - 09:40
ird
User 15/04/2018 - 09:40
weird
User 15/04/2018 - 09:41
it works now
User 15/04/2018 - 09:41
works in here, but not on my spreadsheet
Excelchat Expert 15/04/2018 - 09:41
ok.fine. but you can use simple formula
Excelchat Expert 15/04/2018 - 09:41
=IF(COUNTIFS(I2:J2,"*"&A2&"*")>0,A2,"unknown")
User 15/04/2018 - 09:41
really?
User 15/04/2018 - 09:42
doesn't work
Excelchat Expert 15/04/2018 - 09:42
why answer is storage alias issue
User 15/04/2018 - 09:42
I need it to search columns I thru J
User 15/04/2018 - 09:42
or all 8 key words
Excelchat Expert 15/04/2018 - 09:43
let me share my excel please check
Excelchat Expert 15/04/2018 - 09:43
User 15/04/2018 - 09:44
but it's only searching for TPP
User 15/04/2018 - 09:44
I need it to search for every single keyword
User 15/04/2018 - 09:44
not just one
User 15/04/2018 - 09:44
and return the corresponding return value
User 15/04/2018 - 09:44
based on the key word it finds
Excelchat Expert 15/04/2018 - 09:44
each row value will take and search in correspoding rows?
Excelchat Expert 15/04/2018 - 09:45
A2 compares with I2 and J2
Excelchat Expert 15/04/2018 - 09:45
A3 compares with I3 and J3
Excelchat Expert 15/04/2018 - 09:45
do you want like this or complete column
User 15/04/2018 - 09:45
No
Excelchat Expert 15/04/2018 - 09:45
I:J
User 15/04/2018 - 09:45
A2:A9 compares with I2 and J2
Excelchat Expert 15/04/2018 - 09:45
ok fine.
User 15/04/2018 - 09:45
then depending on whether it finds A2, or A3, or A4, it will put the corresponding result value
User 15/04/2018 - 09:45
so if it finds A2, the result is B2
User 15/04/2018 - 09:45
but if it finds A3, the result is A3
User 15/04/2018 - 09:45
nd if it finds A9, the result is A9
Excelchat Expert 15/04/2018 - 09:46
wait.
User 15/04/2018 - 09:46
Ok..
Excelchat Expert 15/04/2018 - 09:46
Check this.
Excelchat Expert 15/04/2018 - 09:46
Excelchat Expert 15/04/2018 - 09:47
Please check and if you feel it will take moretime extend the time
Excelchat Expert 15/04/2018 - 09:49
is it fine?
Excelchat Expert 15/04/2018 - 09:49
are you there ?
User 15/04/2018 - 09:50
=ArrayFormula(IFERROR(INDEX(\$B\$2:\$B\$9, IFERROR(MATCH(TRUE,ISNUMBER(MATCH("*"&\$A\$2:\$A\$9&"*",\$I:\$I,0)),0), MATCH(TRUE,ISNUMBER(MATCH("*"&\$A\$2:\$A\$9&"*",\$J:\$J,0)),0))),"UNKNOWN"))
User 15/04/2018 - 09:50
hat's the new formula?
Excelchat Expert 15/04/2018 - 09:50
no.
Excelchat Expert 15/04/2018 - 09:50
=IF(COUNTIFS(I:J,"*"&A2&"*")>0,A2,"unknown")
Excelchat Expert 15/04/2018 - 09:50
Refer my sheet
Excelchat Expert 15/04/2018 - 09:50
and extend time.
Excelchat Expert 15/04/2018 - 09:52
??
User 15/04/2018 - 09:52
only searching for A2
User 15/04/2018 - 09:52
needs to search for A2 - A9 my friend
Excelchat Expert 15/04/2018 - 09:52
Ni
User 15/04/2018 - 09:52
and depending on which keyword it finds, the result shouold be A2 - A9
User 15/04/2018 - 09:52
=IF(COUNTIFS(I:J,"*"&A2&"*")>0,A2,"unknown")
Excelchat Expert 15/04/2018 - 09:52
ok.give me 5mintues.
User 15/04/2018 - 09:52
yours is still only searching for A2
User 15/04/2018 - 09:52
:/
Excelchat Expert 15/04/2018 - 09:54
ok. Sorry.
User 15/04/2018 - 09:54
no problem
Excelchat Expert 15/04/2018 - 09:57
in my formula also it will go and check all the keywords correct
User 15/04/2018 - 09:57
yes
Excelchat Expert 15/04/2018 - 09:57
then what will be the problem
Excelchat Expert 15/04/2018 - 09:58
First it will check TPP exists or not
Excelchat Expert 15/04/2018 - 09:58
Second it will check partition or not.
User 15/04/2018 - 09:58
yes
Excelchat Expert 15/04/2018 - 09:58
ya..finally it showing which keyword exists or not
User 15/04/2018 - 09:58
yes correct
Excelchat Expert 15/04/2018 - 09:58
then what will be your issue.
Excelchat Expert 15/04/2018 - 09:59
User 15/04/2018 - 09:59
if column I, or column J, contains any of the keywords between A2:A9, then print return value in B2:B9 for corresponding keyword
Excelchat Expert 15/04/2018 - 09:59
ok .got it
User 15/04/2018 - 10:00
that file has got it wrong
User 15/04/2018 - 10:00
for each row, you are only searching for one keyword
Excelchat Expert 15/04/2018 - 10:01
Check this file
Excelchat Expert 15/04/2018 - 10:02
Is it fine?
User 15/04/2018 - 10:02
no....
Excelchat Expert 15/04/2018 - 10:02
Why?
User 15/04/2018 - 10:02
why does C3 show unknown
User 15/04/2018 - 10:03
when short description contains alias
User 15/04/2018 - 10:03
it shuold be storage alias issue ten
Excelchat Expert 15/04/2018 - 10:03
Dont look at the screen
Excelchat Expert 15/04/2018 - 10:03
I sent you one excel sheet.
Excelchat Expert 15/04/2018 - 10:03
User 15/04/2018 - 10:03
I did
User 15/04/2018 - 10:03
User 15/04/2018 - 10:03
click on C3
User 15/04/2018 - 10:03
press enter
Excelchat Expert 15/04/2018 - 10:03
C3 result is Partition
User 15/04/2018 - 10:03
you will see it does not bring back storage alias issue as it shouold
User 15/04/2018 - 10:03
C3 should not show partition
User 15/04/2018 - 10:04
ere is no 'partition' string in I3 or J3
Excelchat Expert 15/04/2018 - 10:04
ok.now please look at the screen
User 15/04/2018 - 10:04
there is 'alias' string
User 15/04/2018 - 10:04
so the result should be storage alias issue
Excelchat Expert 15/04/2018 - 10:05
=IFERROR(INDEX(\$B\$2:\$B\$9, IFERROR(MATCH(TRUE,ISNUMBER(MATCH("*"&\$A\$2:\$A\$9&"*",\$I:\$I,0)),0), MATCH(TRUE,ISNUMBER(MATCH("*"&\$A\$2:\$A\$9&"*",\$J:\$J,0)),0))),"UNKNOWN")
Excelchat Expert 15/04/2018 - 10:05
Excelchat Expert 15/04/2018 - 10:05
All the result you would like to see "Storage Alias issue?
User 15/04/2018 - 10:05
No
Excelchat Expert 15/04/2018 - 10:06
Column C result in our screen
User 15/04/2018 - 10:06
Search thru Column H and Column I, and IF the one of the two columns CONTAINs TPP, OR Partition, return TPP OR if cell CONTAINs reparent, re-parent, deparent OR de-parent, return Reparenting Issue, OR if cell CONTAINs alias, OR glom, return Storage Alias Issue otherwise, return UNKNOWN.
User 15/04/2018 - 10:06
this is what I'm trying to get
User 15/04/2018 - 10:06
Search thru Column I and Column J, and IF the one of the two columns CONTAINs TPP, OR Partition, return TPP OR if cell CONTAINs reparent, re-parent, deparent OR de-parent, return Reparenting Issue, OR if cell CONTAINs alias, OR glom, return Storage Alias Issue otherwise, return UNKNOWN.
Excelchat Expert 15/04/2018 - 10:06
ok..just a minute
Excelchat Expert 15/04/2018 - 10:09
Please extend time. I will finish it
Excelchat Expert 15/04/2018 - 10:16
working just give me 3mintue
User 15/04/2018 - 10:23
ok
Excelchat Expert 15/04/2018 - 10:24
User 15/04/2018 - 10:25
why is C3 unknown
User 15/04/2018 - 10:25
t should be storage alias
User 15/04/2018 - 10:25
because the word alias is in I and J3
User 15/04/2018 - 10:26
We need to search for ALL keywords in A2 - A9
User 15/04/2018 - 10:26
or every single ROW for Short Description and Description
User 15/04/2018 - 10:26
d put the corresponding return value
Excelchat Expert 15/04/2018 - 10:26
then complete cell result is "Storage alias issu" only correct
User 15/04/2018 - 10:26
yes...
Excelchat Expert 15/04/2018 - 10:26
thats already fixed you told its wrong
User 15/04/2018 - 10:26
nevermind, I will figure it out myself, I don't think you are understanding the request properly
Excelchat Expert 15/04/2018 - 10:27
=ArrayFormula(IFERROR(INDEX(\$B\$2:\$B\$9, IFERROR(MATCH(TRUE,ISNUMBER(MATCH("*"&\$A\$2:\$A\$9&"*",\$I:\$I,0)),0), MATCH(TRUE,ISNUMBER(MATCH("*"&\$A\$2:\$A\$9&"*",\$J:\$J,0)),0))),"UNKNOWN"))
Excelchat Expert 15/04/2018 - 10:27
This is the formula for getting "Storage issue
Excelchat Expert 15/04/2018 - 10:27
now check C column Result.
Excelchat Expert 15/04/2018 - 10:28
I already shared you the correct answer you only informed me its wrong
Excelchat Expert 15/04/2018 - 10:29
here you go your final file
Excelchat Expert 15/04/2018 - 10:31
I hope you didn't see any of my file anyway no issues.
Excelchat Expert 15/04/2018 - 10:31
take care

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.