# IF statement true, when a cell value matches one of the values in a multi-valued cell?

1. ## IF statement true, when a cell value matches one of the values in a multi-valued cell?

I have created a workbook. It has two worksheets (WORKSHEET1, WORKSHEET2). Some of the cells within WORKSHEET2 have multiple text values.

For example:
One cell, in WORKSHEET2, may be equal to ="Monday"&", "&"Tuesday"&", "&"Wednesday"&"" which is then represented as "Monday, Tuesday, Wednesday".

In WORKSHEET1 I have dates in row 1 and week days in row 2. I'm trying to create an IF statement, in row 3, that will check the week day in row 2 and if it matches one of the day values in a given cell, in WORKSHEET2, it will return "1". Otherwise it will return "".

I have created the following so far, but this only works with single value cells.

=IF(C2=Table4[@Column3], 1, "")

I can then apply conditional formating to any cells with the value of "1".

However, as stated this only works with single value cells, and some of my cells have multiple values.

Can I create an IF statement that says if any of the values, in the given multiple-value cell, match the week day in row 2 then produce "1" otherwise produce ""?

2. ## Re: IF statement true, when a cell value matches one of the values in a multi-valued cell?

You can use the Search Function

=ISNUMBER(SEARCH(TEXT(A1,"dddd"),B1))

where A1 is a Date, and B1 is the long string "Monday, Tuesday, Wednesday"
This will give a a True/False result (True if the day of A1 exists in B1)

In conditional formatting, you can drop the ISNUMBER part.

3. ## Re: IF statement true, when a cell value matches one of the values in a multi-valued cell?

Originally Posted by Jonmo1
You can use the Search Function

=ISNUMBER(SEARCH(TEXT(A1,"dddd"),B1))

where A1 is a Date, and B1 is the long string "Monday, Tuesday, Wednesday"
This will give a a True/False result (True if the day of A1 exists in B1)

In conditional formatting, you can drop the ISNUMBER part.

Jonmo1! Thank you very much.

This is what I ended up using ... =IF(ISNUMBER(SEARCH(TEXT(C1, "dddd"), Table4[@Column3])), "TRUE", "")

It works fine without the additional IF statement I added. However, this produced a value of "TRUE" if true and a value of "FALSE" if false. I wanted to have the value of "" if false. Adding the IF statement allowed me to specify the values I wanted for true and false results.

4. ## Re: IF statement true, when a cell value matches one of the values in a multi-valued cell?

Glad to help, thanks for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1