IF.OR VS IF.ISERROR.LOOKUP: In need of a better formula

Usernameusername

New Member
Joined
Oct 5, 2016
Messages
2
Hello,

I have looked hell over for a solution to my problem.

I have a matrix containing a column of text mixing dates and text. I want it so that my formula copies a cell to its left if it contains either monday, or tuesday, or any day of the week, and leaves the left cell blank otherwise. The source cell contains not only the day of the week but also the date (Tuesday January 5th.)

x | Monday blabla
x | Tuesday blabla
x | Wednesday blabla
x | Thursday blabla
x | Friday blabla

I thought of IF.OR. It allows me to enter multiple arguments for every day of the week. However, it seems look for the keyword exactly, rather than the keyword simply being contained along other text within the cell.

Looking for a solution, I found out about IF.ISERROR.LOOKUP. It looks like this (excuse my french):

=SI(ESTERREUR(CHERCHE("*lundi*";D4));"";D4)

This works to find the word within the cell and not the cell being exactly as the searched word. However, I need it to look for more than one word for every cell. All of the days of the week. Here is an example of one of my many attempts; I've made a ton of variants with it.


=SI(ESTERREUR(CHERCHE("*lundi*";"*mardi*";D4));"";D4)

I am at a loss and that's why I'm here. Thanks so much :)


M


P.S.: I downloaded Mr Excel HTML maker but the program doesn't even open when prompted, so please bear with my ugly little example.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
Welcome to Mr Excel Forum

Maybe this
English
=IF(SUM(--ISNUMBER(SEARCH({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"},D4))),"x","")

French
=SI(SOMME(--ESTNUM(CHERCHE({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"};D4)));"x";"")

Hope this helps

M.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
P.S.: I downloaded Mr Excel HTML maker but the program doesn't even open when prompted,
The MrExcel HTML Maker is an Add-In that resides within Excel. Post #3 in Attachments has written instructions and a video that detail how to install & use it.
 

Usernameusername

New Member
Joined
Oct 5, 2016
Messages
2
Welcome to Mr Excel Forum

Maybe this
English
=IF(SUM(--ISNUMBER(SEARCH({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"},D4))),"x","")

French
=SI(SOMME(--ESTNUM(CHERCHE({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"};D4)));"x";"")

Hope this helps

M.

Thank you so much Marcelo! I didn't know I could use brackets to include multiple arguments. Your help is greatly appreciated, saves my face!

The MrExcel HTML Maker is an Add-In that resides within Excel. Post #3 in Attachments has written instructions and a video that detail how to install & use it.

Oh, I understand now. Thanks :)
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405

ADVERTISEMENT

You are welcome. Thanks for the feedback.

M.
 

dvahon

New Member
Joined
Nov 29, 2006
Messages
18
Hi Marcelo,
I am looking for a similar formula except I want it to return the day of the week that it found. So if it found Monday, return Monday. If it found Tuesday, return Tuesday etc.
Thanks
D
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405

ADVERTISEMENT

Hi Marcelo,
I am looking for a similar formula except I want it to return the day of the week that it found. So if it found Monday, return Monday. If it found Tuesday, return Tuesday etc.
Thanks
D

Try this


A
B
1
Text​
Result​
2
Monday blabla​
Monday​
3
Tuesday blabla​
Tuesday​
4
Wednesday blabla​
Wednesday​
5
Thursday blabla​
Thursday​
6
Friday blabla​
Friday​

Formula in B2 copied down
=LOOKUP(9.99E+307,SEARCH({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"},A2),{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"})

M.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
In my formulas below, you may need to do some , to ; conversions for your language version.

For the first problem, I think this slightly simpler formula should also do the job

=IF(COUNT(SEARCH({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"},D4)),"x","")



For the second problem, IF the Monday/Tuesday etc is always first in the cell (if it exists at all in the cell) like all your samples, then there is also this shorter one

=IF(COUNT(SEARCH({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"},D4)),LEFT(D4,FIND(" ",D4&" ")-1),"")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,160
Messages
5,594,597
Members
413,916
Latest member
Islandlady

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top