SOLVED

Writing an ELIF statement with multiple ANDs

Copper Contributor

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.

Screenshot 2023-10-23 111923.png

 

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

@RexMorgan2115 

Perhaps you mean

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

@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

@RexMorgan2115 

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)

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

@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

@RexMorgan2115 

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)

View solution in original post