I'm trying to set up a formula to do the following. I would like cell E90 to
give a text output. The text is dependent upon 2 other cells in the workbook
(Q88 and T87). I want the text in E90 to say "Full Discount Achieved" if Q88
and T87 reach the highest bucket. Otherwise, if either of cells Q88 or T87
do not have a value in the highest bucket, I want the text output to say "X
amount in Q88 or Y Amount in T87 needed to reach next discount level". There
are 3 buckets for Q88 and 6 buckets for T87. I've tried using the IF
formula, but I don't know how to get the output to show how much is needed to
achieve the next bucket, rather than the full discount.

Sheet 1 contains the buckets and another section of the workbook uses the
VLOOKUP formula to determine which discount level is achieved.

Any help is greatly appreciated. Thanks!

To get this to work, I had to add a zero bucket to the beginning of each
bucket range, so the 3-cell bucket range becomes 4 cells, and the 6-cell
range becomes 7 cells. I put the 4-cell range of buckets for Q88 in AA1:AA4,
and the 7-cell range of buckets for T87 in AC1:AC7. Then I entered this
formula in E90:

=IF(AND(Q88=MAX(AA1:AA4),T87=MAX(AC1:AC7)),"Full discount
achieved",IF(ISERROR(INDEX(AA1:AA4,MATCH(Q88,AA1:A A4)+1,1)),0,INDEX(AA1:AA4,MATCH(Q88,AA1:AA4)+1,1)-Q88)&"
in Q88 or
"&IF(ISERROR(INDEX(AC1:AC7,MATCH(T87,AC1:AC7)+1,1) ),0,INDEX(AC1:AC7,MATCH(T87,AC1:AC7)+1,1)-T87)&" in T87 needed to reach next discount level")

You would need to edit the 4-cell and 7-cell bucket ranges to match your
workbook. If you named the 4-cell range Rng1 and the 7-cell range Rng2, you
could use this formula in E90:

=IF(AND(Q88=MAX(Rng1),T87=MAX(Rng2)),"Full discount
achieved",IF(ISERROR(INDEX(Rng1,MATCH(Q88,Rng1)+1, 1)),0,INDEX(Rng1,MATCH(Q88,Rng1)+1,1)-Q88)&"
in Q88 or
"&IF(ISERROR(INDEX(Rng2,MATCH(T87,Rng2)+1,1)),0,IN DEX(Rng2,MATCH(T87,Rng2)+1,1)-T87)&" in T87 needed to reach next discount level")

Hope this helps,

Hutch

"Taylor" wrote:

I'm trying to set up a formula to do the following. I would like cell E90 to
give a text output. The text is dependent upon 2 other cells in the workbook
(Q88 and T87). I want the text in E90 to say "Full Discount Achieved" if Q88
and T87 reach the highest bucket. Otherwise, if either of cells Q88 or T87
do not have a value in the highest bucket, I want the text output to say "X
amount in Q88 or Y Amount in T87 needed to reach next discount level". There
are 3 buckets for Q88 and 6 buckets for T87. I've tried using the IF
formula, but I don't know how to get the output to show how much is needed to
achieve the next bucket, rather than the full discount.

Sheet 1 contains the buckets and another section of the workbook uses the
VLOOKUP formula to determine which discount level is achieved.

Any help is greatly appreciated. Thanks!

Hutch-

Thanks so much! That's perfect.