SOLVED

# Writing an ELIF statement with multiple ANDs

Copper Contributor

# Writing an ELIF statement with multiple ANDs

Hi all,

I'm working on trying to write an ELIF statement that evaluates with multiple ANDs. I'm struggling with the syntax, and whether I should be using IFS, XLOOKUP, or something else.

For the problem:

Say that I have a company, and different sales people are assigned clients based on the client's income. If a household has 3 people, and the total income is between 0 and 600, then Sarah will be the salesperson.

I am looking to fill in column I with the salesperson.

I need to write a statement that says IF G2=B2, AND D2>H2>C2, then I2=A2. IF D5>H2>D5, I2=A5, etc down to IF H2>=C17, then I2=A17.

I tried an IF statement:

IF(AND(I2=A2, D2>H2>C2), A2)

but I couldn't figure out the nesting syntax for multiple AND statements.

If anyone has any insight, I would greatly appreciate it. Thank you!

7 Replies

# Re: Writing an ELIF statement with multiple ANDs

I see the XLOOKUP reference so I assume you have 365. I did not test this but try something like:
=let(LUtab, FILTER(TABLE, TABLE[Household]=[Household],""), XLOOKUP(Income, INDEX(LUtab,,3), INDEX(LUtab,,1),"",-1))
this assume both are formatted as tables and the one on the left is Named TABLE.

# Re: Writing an ELIF statement with multiple ANDs

Did you ever figure this out? I have a similar problem. This is my formula that I am struggling with --> =IF(AND(15>A3>=10,15<=B2<=35),"true","false") Cell b2 contains a numeric value of 28 and cell a3 contains a numeric value of 10. The formula produces a result of "false" although I am expecting a value of "true"

# Re: Writing an ELIF statement with multiple ANDs

Perhaps you mean

``=IF((A3>=10)*(A3<15)*(B2<=35)*(B2>=15),true,false)``

# Re: Writing an ELIF statement with multiple ANDs

@Sergei Baklan thanks for the reply. I am trying to evaluate cell a3 and cell b2. The range that I want to see in a3 is a value between 10 and 15 inclusively and a value in cell b2 between 15 and 35 inclusively. If both of those conditions are met then write "true" if not then write "false". That is why I have those conditions formatted the way that I do,

i.e. condition 1 --->    15>A3>=10 (15 is greater than cell A3 and cell A3 is greater than or equal to 10)

condition 2 --->    15<=B2<=35 (15 is less than or equal to cell B2 and cell B2 is less than or equal to 35)

best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Writing an ELIF statement with multiple ANDs

Did you check suggested formula if it works for you?

In Excel you can't use in formula "15>A3>=10", that could be

AND(A3>=10, A3<15)

or in another notation

(A3>=10)*(A3<15)

# Re: Writing an ELIF statement with multiple ANDs

Thanks for replying again. That does indeed work! I don't understand how it works but it does.

# Re: Writing an ELIF statement with multiple ANDs

@RexMorgan2115 , you are welcome.

In Excel any number but 1 is equivalent to TRUE if you use it in logical expression. And the opposite, TRUE converted to number is 1. Thus

TRUE*TRUE*TRUE = 1*1*1 = 1 = TRUE

TRUE*FALSE*TRUE = 1*0*1 = 0 = FALSE

In addition, each part of logical expression could use only single condition. Other words,

5<v<10 is of wrong syntaxis. Correct one is combining of two conditions

v>5 AND v<10 which could be translated in Excel formula as

(v>5)*(v<10), or in more formal way

AND( v>5, v < 10)

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Writing an ELIF statement with multiple ANDs

Did you check suggested formula if it works for you?

In Excel you can't use in formula "15>A3>=10", that could be

AND(A3>=10, A3<15)

or in another notation

(A3>=10)*(A3<15)