Thread: Lookup then find the value less than or equal to...

I am stumped I've been trying everything I can to make this work, is this possible?

Ok so I have one sheet with two columns the Item number in A and the Qty sold in B.

On the second sheet in the same workbook, I have a list of item numbers in A, a list of qty's in C, and a list of prices in D.

I need to find the Item from sheet A, see if it's qty sold is greater than or equal to the quantity price breaks in C and display the price from D

So if i have a Widget and sold 23 and on the second sheet I had

Widget 1 $12.00
Widget 3 $11.49
Widget 10 $10.99
Widget 250 $9.45

it would find the Item Widget find that the qty is greater than or equal to 10 and display $10.99

Originally Posted by lukep10

I am stumped I've been trying everything I can to make this work, is this possible?

Ok so I have one sheet with two columns the Item number in A and the Qty sold in B.

On the second sheet in the same workbook, I have a list of item numbers in A, a list of qty's in C, and a list of prices in D.

I need to find the Item from sheet A, see if it's qty sold is greater than or equal to the quantity price breaks in C and display the price from D

So if i have a Widget and sold 23 and on the second sheet I had

Widget 1 $12.00
Widget 3 $11.49
Widget 10 $10.99
Widget 250 $9.45

it would find the Item Widget find that the qty is greater than or equal to 10 and display $10.99

i think function match can help you

http://www.techonthenet.com/excel/formulas/match.php

OK i tried =MATCH(I3,Sheet2!C:C,-1) where I3 is the item Number and C:C on sheet2 is the range of quantities but I don't see where it's going to look for the Qty listed in K3. Sorry i've never used the match function and I'm totally lost.

Hot damn! I figured it out!

{=IF(ISERROR((INDEX(Sheet2!$D:$D,MATCH(1,(I2=Sheet2!$A:$A)*(K2>=Sheet2!$C:$C),0)))),0,(INDEX(Sheet2! $D:$D,MATCH(1,(I2=Sheet2!$A:$A)*(K2>=Sheet2!$C:$C),0))))}