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(I6>125000,J6>225000,L6>100000),O6,”50000”IF(AND(I6>75000,J6>150000,L6>45000),O6,”30000”,IF(AND(I6>50000,J6>100000,L6>30000),O6,”16000”,IF(AND(I6>25000,J6>40000,L6>10000),O6,”8000”,IF(AND(I6>10000,J6>20000,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:

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/spreadsheets/much-nesting-formula-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"))))))))

This will expand up to about 28 items, just add to each array in the correct position, and remember the first array is ordered.

=LOOKUP(F20, {0,50000,100000,200000,300000,500000,750000,100000 0,1250000}, {"no way","*","**","***","****","*****","Silver","Gold","Platinum"})

Or, you could put that in a table in your sheet and just refer to it with cell ranges:

M N
1 0 no way
2 50000 *
3 100000 **
4 200000 ***
5 300000 ****
6 500000 *****
7 750000 Silver
8 1000000 Gold
9 1250000 Platinum

=LOOKUP(F20, $M$1:$M$9, $N$1:$N$9)

I would not have concluded that these two threads constituted the same thing. The first question was FAR more complicated than the current topic.

The first question I posted was more complicated, I had to have a number that posted, but there are two separate categories, that is why the first is more complicated. The current topic was easier for me, and thank you for all yor help