working out unit price

[ainganni]

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.  

[#2 · Accepted solution]

@ainganni 

Try it this way:

[#3]

thank you so much! exactly what I wanted