Forum Discussion
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!
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)
- 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"
SergeiBaklan 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)
