working out unit price

Copper Contributor

Hi, newbie poster here so be nice!


I have a set of data which sets out the different prices for an item based on volume.  what I want to do it look up a specified value in this range to return the correct unit price. example of data is:


the formula =SUMPRODUCT(--(C10>B1:G6),B1:G6)   where C10 = 35000, works to an extent but returns a value of 18 which is the sum of the first 4 columns.


what I want is a function which returns 3 as that is is the unit price rather than the sum of all the unit prices for values less than 35000 in this case.  

2 Replies
best response confirmed by ainganni (Copper Contributor)


Try it this way:



thank you so much! exactly what I wanted