VLOOKUP, MINIMUM to zero everytime?

I barely follow what you are asking.

Wouldn't your category ranges be this?
A 0 100
B 101 400
C 401 900
D 901 1700

A is the first 100
B is the next 300
C is the next 500
D is the next 800

Formula
=LOOKUP(E4,{0,101,401,901,1701},{"A","B","C","D","NEXT"})

After looking at your question closer and assuming my above answer is correct, you could use another column to calculate the upper limit for each Range-Category and then use that in the LOOKUP formula. In the example below, I used column I because it was empty.

Great! Many thanks AlphaFrog, your FORMULAs for COLUMN F & COLUMN I, work as intended. SPREADSHEET modified

I need your expertise to answer, a few other questions about how to incorporate the following:

~ In COLUMN N, the formula needs editing (If possible) to show the ACTUAL of how much £VALUE went into that CATEGORY per RANGE, if nothing, then 0.
EXAMPLE: If row 2 value was 320, then N2=0, N3=0, N4=320
So that the summary table (M25 to P25) can accurately add the number of CATEGORYs used per RANGE.

~ Following CATEGORIES may be the SAME value, or LESS value then the previous CATEGORY, what formula do I need to consider this? (Or in other words, the MAX values may NOT necessarily be in LOW to HIGH range value, or > then previous. COLUMN K ~ min & COLUMN L ~ max)
EXAMPLE: CAT A= 0 to 100, CAT B= 0 to 100, CAT C= 0 to 200, CAT D= 0 to 200.
For example if row 2 running total value was 99, and row 3 running total value was 197, then F2 = A i 1, then F3 = A i 2
Is it possible to have a formula to perform this complicated scenario calculation?

Many thanks

Stephan

Put this in N2 and copy it down column N

=SUMIF(F:F,M2,E:E)

This will sum the values from column E if the values in column F match M2

This assumes each category in column M has a unique identifier as I have them in the example above e.g.; A i

Thanks AlphaFrog, THANKS for formula, needed for Column D
=SUMIF(F:F,M2,D:D)

Q1. How to edit to check 2 columns?
As above but F:F, M2, G:G, J2, D:D

Q2. The formula in COLUMN F =LOOKUP(E2,$U$2:$U$21,$M$2:$M$21), sometimes miscalculates and exceeds the MAX of the specific category, can the formula(s) be edited so CATEGORIES aren't exceeded at all?
See an example of this error, the N4 SUMMARY of datatable D6:D9.

What formula do I need in COLUMN F and COLUMN U, to stop this error from happening?

Cheers

Stephan

Q1:
The SUMPRODUCT formula you are currently using in column N is a two condition SUMIF-type formula. You can adapt it to do your SUMIF F:F, M2, G:G, J2, D:D

Q2:
The LOOKUP formula is not "miscalculating". Category C is getting an extra 100 because Category B stopped at 200 and not 300. When the category B running total is at 200, the next number in column D (D6) is then 200. This makes all of that 200 go to the next category C because it makes Category B over the limit. That's where the extra 100 is coming from.

If you make a lookup formula so that F6 is a B, then Category B will add up to 400 (100 over the limit).

So the question is how do you want to split up cell D6 when half of it is in Category B and half of it is in Category C ?

Hi AlphaFrog, ok.

Q1. In formula how is that written?

Q2. Into CATEGORY C only, not any into CATEGORY B (D6 example).
The MAX in column L, to compare is like a glass that can't be over filled. Hence the formula is to start with the next CATEGORY, and leave the previous CATEGORY only partially filled.

Cheers for your help

Stephan.

Re: VLOOKUP, remainder issue!

Hi AlphaFrog

Quick question, in my spreadsheet with your LOOKUP formula, how can the REMAINDER issues be resolved?
(Problems arise when maximum of categories not used within a range, then following categories exceed their maximum)
See M4 or M5 in most recent spreadsheet

OUR PREVIOUS THREADS RELEVANT TO "THIS" THREAD.

MY EMAIL:
The formula in COLUMN F =LOOKUP(E2,$U$2:$U$21,$M$2:$M$21), sometimes miscalculates and exceeds the MAX of the specific category, can the formula(s) be edited so CATEGORIES aren't exceeded at all?
YOUR EMAIL:
The LOOKUP formula is not "miscalculating". Category C is getting an extra 100 because Category B stopped at 200 and not 300. When the category B running total is at 200, the next number in column D (D6) is then 200. This makes all of that 200 go to the next category C because it makes Category B over the limit. That's where the extra 100 is coming from.

If you make a lookup formula so that F6 is a B, then Category B will add up to 400 (100 over the limit).

So the question is how do you want to split up cell D6 when half of it is in Category B and half of it is in Category C ?

MY EMAIL:
Into CATEGORY C only, not any into CATEGORY B (D6 example).
The MAX in column L, to compare is like a glass that can't be over filled. Hence the formula is to start with the next CATEGORY, and leave the previous CATEGORY only partially filled.

Cheers Stephan