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
Can you please share your excel
Excelchat Expert
15/04/2018 - 09:34
Are you there?
User
15/04/2018 - 09:34
Yes
Excelchat Expert
15/04/2018 - 09:35
Can you please forward your excel
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
Please explain your question slowly..
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
Please check
[Uploaded an Excel file]
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.
[Uploaded an Excel file]
Excelchat Expert
15/04/2018 - 09:46
Countifs itself enough please check the answer
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
Please download this file and check
[Uploaded an Excel file]
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
[Uploaded an Excel 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
Down load and check
User
15/04/2018 - 10:03
I did
User
15/04/2018 - 10:03
I downloaded and checked it
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
Is it your expected result?
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
Please check this file
[Uploaded an Excel file]
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
[Uploaded an Excel 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.
