Double nested lookup maybe?

[GFAlexander]

I have a named range which contains the following data:

Column 1 Product code
Col 2 Product description
Col 3 Retail Price
Col 4 Price quantity 1
Col 5 Quantity 1 price
Col 6 Price quantity 2
Col 7 Quantity 2 price

etc.

My input sheet is as follows:

Col 1 Product code
Col 2 Product description - Vlookup on code
Col 3 RRP - Vlookup on code
Col 4 Quantity required - number, free input
Col 5 Price

I need column 5 to first of all check column 1 and if it's empty do nothing, then to check column for the entered number then compare this number to "quantity price 1" in the named range and if it is less than or equal to it return "price quantity 1" from the range. If it is greater than "quantity price 1" it should check against "quantity price 2", and if it's less than that return "price quantity 2".

I hope that makes sense, let me know if anyone can help or if you need more information.

Thanx

Gary

 

[iGrant]

I would think you could do this with a VLOOKUP, that checks the product code entered in your input sheet and returns the price from your named range. You should be able to fix the last-but-one argument of the function to refer to the quantity, something like:

=VLOOKUP(A2,NamedRange,D4+4,false)

Assuming A2 is your 'Product Code' input
and D4 is your quantity input
'false' argument to ensure a direct match
You need this function in your RRP cell on your input sheet