Hi all.


I´m trying to figure out how to find the price of something in a list that depends of the price break reached...and the break point change with each item.

Reference Description Breakpoint €/kg Breakpoint €/kg
A Apples 1 2 3 1.5
B Bananas 1 3 6 2

So here if I use VLookup to a fix break point is fine but I do not know how to use when the breakpoint changes

Please have a look at the file attached

Cheers.
Adrian

Attached Files

Prices with different breakpoint.xlsx (8.8 KB, 10 views)

Try:

=IF(A3="","",LOOKUP(2,1/(($H$2:$L$2="Breakpoint")*(INDEX($H$3:$L$5,MATCH($ B3,$F$3:$F$5,0),0)<=$A3)),INDEX($I$3:$M$5,MATCH($B 3,$F$3:$F$5,0),0)))

Thanks NBVC
I´m afraid that your solution did not work.
However this one yes:
=IF(AND(A4>1;A4<VLOOKUP(B4;F3:M5;7;FALSE));VLOOKUP (B4;F3:M5;6;FALSE);0)
Cheers

An working example is in attached file. I changed the starting break-points from 1 to 0, and in formulas added a calculated MAX break-point value as 999999 (it may be any value Excel can interprete as number, and which is bigger than any real break-point you may have).

I left the reference untouched, but really you can use fruit names instead directly.

Attached Files

Copy of Prices with different breakpoint.xlsx (13.9 KB, 10 views)

Yep your solutions works very well.
It´s good idea.
Thanks for your time and effort

Here is my solution applied to your original data... test it.

Maybe you didn't change the commas in my formula to semi colons to accomodate to your Excel version?

Attached Files

Copy of Prices with different breakpoint.xlsx (9.2 KB, 9 views)