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!


1 Reply
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.