**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.*