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