Forum Discussion
Nested IF with XLOOKUP
I have a worksheet that I use to create rates for an insurance quote. I put the person's Age and Annual income in and it tells me how much of the insurance (in dollars) they qualify for (that is jus...
First, I would separate the From/To ages into separate cells. So, I would set up the table like below on Sheet2:
Then, on Sheet1, were A2=54, B2=30000, C2=900, you could enter this formula in D2 to perform your conditional tests and multiply it out (assuming you have office 365):
=SUM((Sheet2!$B$2:$D$2<=A2)*(Sheet2!$B$3:$D$3>=A2)*(Sheet2!$A$4:$A$8=C2)*Sheet2!$B$4:$D$8)
If you don't have office 365, then I would just change "SUM" to "SUMPRODUCT".
=SUMPRODUCT((Sheet2!$B$2:$D$2<=A2)*(Sheet2!$B$3:$D$3>=A2)*(Sheet2!$A$4:$A$8=C2)*Sheet2!$B$4:$D$8)