Attempted to set up formula for IFS statement using OR but Excel is saying invalid

Hi,

Just looking to get a check on this formula, I have underlined where I think I've gone wrong but if someone could tell me what has gone wrong and how to correct, I'd appreciate it, thanks.

Formula:

Please Login or Register  to view this content.

All The Best,
Dan

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

I have responded to your PM. I am not 100% happy with your latest title, but will accept it for now until you have read my PM.

It's very hard to debug formulae sometimes without seeing them in context, but I'd try removing bits of parenthesis first:

=IFERROR(OR(IFS(C4=1,SETTINGS!A20,E4=1, SETTINGS!A21, G4=1, SETTINGS!A22, I4=1, SETTINGS!A23),IFS(D4=1,SETTINGS!A20,F4=1, SETTINGS!A21, H4=1, SETTINGS!A22, I4=1, SETTINGS!A23))))

What are you expecting this formula to do? If you use OR, you need statements (formulae) that will return TRUE or FALSE - this doesn't look like it will do that, but without seeing it in situ, it's really hard to tell.

Attach a workbook.

There's no need to specify a template. All you have to do is ask yourself: what am I trying to do and what's going wrong? That's your thread title. New title is MUCH better - thanks.

I will have a look, but you still haven't said what you are expecting the formula to do ...

Sorry - I cannot troubleshoot this, Dan.

1. The formula refers to a tab called SETTINGS - there is no such tab.
2. You still haven't explained in any clarity what you want the formula to do.

At the moment it's a dog's dinner (sorry), and I cannot see for the life of me what you are attempting to achieve.

Please explain in WORDS what you want the formula to do.

Does this do what you want?

=LOOKUP(INDEX($C$3:$I$3,MATCH(1,$C4:$I4,0)),{"APP","ILL 1","ILL 2","PH 1","PH 2","UH 1","UH 2"},{"Appointment","Ill","Ill","Paid Holiday","Paid Holiday","Unpaid Holiday","Unpaid Holiday"})

Dan - you are not going to like this.

Using multiple nested IF or IFS is NOT the way forward. And no, it isn't actually easier to change things down the line.

I will mock up an example of something that will be much easier for you. Back in five minutes.

Attached is a much easier solution.

You need a lookup table (can be on a separate sheet):

Excel 2016 (Windows) 32 bit

	L	M
3	PH	Paid Holiday
4	UH	Unpaid Holiday
5	ILL	Ill
6	APP	Appointment

Sheet: JAN

Then you use this formula:

=IFERROR(LOOKUP(1000,SEARCH($L$3:$L$6,INDEX($C$3:$I$3,MATCH(1,$C4:$I4,0))),$M$3:$M$6),"")

INDEX($C$3:$I$3,MATCH(1,$C4:$I4,0)) - this returns the header above the 1 in the row

IFERROR(LOOKUP(1000,SEARCH($L$3:$L$6 - this looks at the lookup list in the lookup table and finds a match for the header

),$M$3:$M$6),"") - this returns the value next to the lookup text in the lookup table.

Use Evaluate Formula on the Formulas ribbon to step through and see what it's doing.

Get into the habit of using lookup tables instead of nested IF statements, otherwise you will forever be getting yourself into a mess, like you did today. Be prepared to expand your horizons which will, in the long run,make things MUCH easier for you.

Originally Posted by danjackson5

T

IFERROR - Standard blank any errors

IF C4 OR D4=1, Show SETTINGS!A20

IF E4 OR F4=1, Show SETTINGS!A21

IF G4 OR H4=1, Show SETTINGS!A22

IF I4=1, Show SETTINGS!A23 (There's Only 1 Column for this one)

.

If I understood correctly, your request translates into the formula like this:

Formula:

Please Login or Register  to view this content.