Choose from a column dependent upon a value

Please see attached. I want the formula in G2 to say: IF the value in G11 is less than 250, then display "Below min order value", IF between 250-£499, return adjacent value in column B, IF between £500-£999, return column C, IF between £1000-£1999, return column D, IF above £2000, return column E.
Book3.xlsx

You can put a nested If statement in H2: =IF($G$11<250,"Below min order value",IF($G$11<500,B2,IF($G$11<1000,C2,IF($G$11<2000,D2,E2))))

The problem is that because C11 is summing column C, it is creating a circular reference. Any ideas on how to solve this?
Book4.xlsx

Sorry, I mean G11 and column G.

That's why I suggested to put the formula in column H, not G. There is a way to allow circular references, but you need the iterations defined. File>Options>Formulas>Enable iterative calculation. However, that won't solve this problem as the total for G11 will always be 0 because G11 just sums everything above it, which the line item totals in your first workbook post would be over-written by the formula I wrote if put in G.

So I think the best bet would be to put the formula in H, or if you want to keep it in G, you need to change the total order value formula to:

Formula:

Please Login or Register  to view this content.

and put this formula in G2:

Formula:

Please Login or Register  to view this content.

Sorry Jeff, I haven't managed to do this. I will put it in the Commercial Services section and you can complete the form there for me if you wish. Many thanks.