In excel I have 8 different categories for customer level, but to get to any one level there is three different categories that they must meet. I must have all 8 levels associated in one cell while at the same time evaluating the customers numbers to see if they qualify for any of the 8 levels. This is what I came up with =IF(AND(I6>325000,J6>575000,L6>250000),O6,”13”,IF( AND(I6>250000,J6>450000,L6>200000),O6,”12”,IF(AND( I6>200000,J6>350000,L6>150000),O6,”75000”,IF(AND(I 6>125000,J6>225000,L6>100000),O6,”50000”IF(AND(I6> 75000,J6>150000,L6>45000),O6,”30000”,IF(AND(I6>500 00,J6>100000,L6>30000),O6,”16000”,IF(AND(I6>25000, J6>40000,L6>10000),O6,”8000”,IF(AND(I6>10000,J6>20 000,L6>5000),O6,”3000”))))))))).

O6 is where I want the formula to be evaluated at and populate the correct # category.

In excel I have 8 different categories for customer level, but to get to any one level there is three different categories that they must meet. I must have all 8 levels associated in one cell while at the same time evaluating the customers numbers to see if they qualify for any of the 8 levels. This is what I came up with =IF(AND(I6>325000,J6>575000,L6>250000),O6,”13”,IF( AND(I6>250000,J6>450000,L6>200000),O6,”12”,IF(AND( I6>200000,J6>350000,L6>150000),O6,”75000”,IF(AND(I 6>125000,J6>225000,L6>100000),O6,”50000”IF(AND(I6 > 75000,J6>150000,L6>45000),O6,”30000”,IF(AND(I6>500 00,J6>100000,L6>30000),O6,”16000”,IF(AND(I6>25000, J6>40000,L6>10000),O6,”8000”,IF(AND(I6>10000,J6>20 000,L6>5000),O6,”3000”))))))))).

O6 is where I want the formula to be evaluated at and populate the correct # category.

Forget the formula and explain the tiers and the logic. This will most likely be a MUCH shorter formula with only a couple of IFs once you explain the tiers.

You're trying to put one of these values into O6?
13
12
75000
50000
30000
16000
8000
3000

REMOVED - wrong, I was oh, so wrong. See below for final solution.

Yes, so one of those values will go into O6, based on if the three categories it is evaluating meets the requirements on all three levels. Thanks for the help I will try it.

Woah, Nelly, that didn't work. My apologies, I read the whole thing backwards. Duh. First, for a lookup like this to work, the table has to be sorted ASCENDING, like so:

Code:

---

          Q          R          S          T
1        10000        20000        5000        3000
2        25000        40000        10000        8000
3        50000        100000        30000        16000
4        75000        150000        45000        30000
5        125000        225000        100000        50000
6        200000        350000        150000        75000
7        250000        450000        200000        12
8        325000        575000        250000        13

---

Next, the formula in O6 would have to be this little beast:

=INDEX($T$1:$T$8, MATCH($L6, $S$1 : INDEX($S$1:$S$8, MATCH($J6, $R$1 : INDEX($R$1:$R$8, MATCH($I6, $Q$1:$Q$8, 1), 1), 1))))

So these are the amounts in i,j,k 6 and these numbers go up throughout the year and as they reach the matching corresponding levels i want it to populate the number that is given in t on the table you made, and have that answer go into o6

$400 $9,075 $3,342

It works, your amazing, thank you thank you thank you!!

So I understand everything you did, except for the 1),1)1,) at the end could you tell what this does so that I know for furture reference. Thanks again

No, I think you're misreading it. I colored that originally here in the forum in hopes of making it clear which section constitute a logic section.

It's not 1,1,1. Look at the coloring I did above again and you'll see that each one of those 1s is the final parameter in a MATCH() formula.

The final parameter in a MATCH() formula is 1 or 0, 0 means "exact match required", and 1 means "fuzzy match ok...round down in chart to closest value."

The first 1 feeds the "fuzzy" instruction to the search in column Q. The second 1 feeds it to the search in column R, and the third instructs it to the search in column S.

The formula is incorrect, I entered into
A cell of a spreadsheet and nothing happened.
Go back and see where you are going wrong.

If you want the result to appear in O6 the formula needs to be in O6. So that part of your formula is incorrect.

When building a complex IF statement like this, its best to build it up piecemeal. Build it one clause at a time, then combine them one at a time.

For example you start with (in O6):

=IF(AND(I6>325000,J6>575000,L6>250000,”13”,"False" )

Then create another IF in a separate cell for the False condition. If it works, then substitute that for "False" in O6. Then repeat the process for each condition to be tested.

Sorry you didn't notice that this was a duplicate thread to here:
https://www.askmehelpdesk.com/spread...la-397026.html
Final solution is in post #5.

I have an excel spreadsheet formula that works wonderfully, but I would like if the conditions have not been meet for it to read something other than "False". Is this possible?

You mean somethiig like this:

=IF(2=3,5,"no way")

If 2=3, it returns "5" otherwise it returns "no way"

The formula is already so long that when I tried to enter another if staement so there would be no false returns it was too much for excel

=IF(G6>1250000,"Platinum",IF(G6>1000000,"Gold", IF(G6>750000,"Silver", IF(G6>500000,"*****", IF(G6>300000,"****", IF(G6>200000,"***", IF(G6>100000,"**", IF(G6>50000,"*"))))))))

This is my formula. In the end anything less than 50000 returns false. Excel will not allow me to add onto this formula to return a different " " answer. Is there any other way to change the outcome of "false" when the number is less than 50,000

If you're evaluating the same cell over and over with multiple IF statements trying to match a particular value or text string, then IF statements are the wrong way. This is what LOOKUP and VLOOKUP and INDEX/MATCH is all about.

Post up your workbook and point out the issue, or at lease post up your current formula and desired additions. I'm sure we can offer something far more robust than IF/IF/IF/IF...

Helpinlasvegas. Keep the same topics togeter as threads.

Read "Answer this question" as "Reply to this question". It's the fault of the software makers.

I've asked that your threads be combined.

=IF(G6>1250000,"Platinum",IF(G6>1000000,"Gold", IF(G6>750000,"Silver", IF(G6>500000,"*****", IF(G6>300000,"****", IF(G6>200000,"***", IF(G6>100000,"**", IF(G6>50000,"*","no way"))))))))