Excel formula amendment - tiny change :)

[midnightcaller]

Hi,

I basically have a volume based price list >

0-30 = R10.00
31-100 = R9.00
100-500 = R8.00

But if it's 0 - how do I make it pull up R0.00 ?
Basically if B19 = '0', then make cell '0'

Right now '0' is pulling the under 30 rule

This needs to change to
0 =R0.00
1 - 30 = R10.00
31-100 = R9.00
101-500 = R8.00

 

[swift412]

If your version of Excel has =SORTBY, you can sort the red range in-formula using something like

SORTBY($D$1:$E$10, $D$1:$D$10)

or even better put it in a table and have nicer syntax like

SORTBY(Red, Red[Num])

Then again, if you have access to SORTBY, you probably have access to XLOOKUP, which seems to handle the sorting properly out of the box.