Writing an ELIF statement with multiple ANDs

[cwhite1918]

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!

[#2]

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.