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:
| 5000 | 10000 | 20000 | 30000 | 40000 | 50000 |
| 6 | 5 | 4 | 3 | 2 | 1 |
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.
