Can this be done with lookup?

Here are two solutions:

http://articles.excelyogi.com/multip...el/2009/03/16/

http://articles.excelyogi.com/vlooku...el/2009/02/09/

If you wish for an alternative to these, then google: excel vlookup multiple criteria

There you will find many hits.

Alan

Would you explain the relationship between the quantity ordered and the item code as the last three don't seem logical to me.

Alan

Part of the problem might be that all of your lookup values are A1 descending except 1001 which is A1 descending,

All must be either Ascending or Descending.
(I have assumed Descending as this is the majority.)

Try this rather crude formula to see if it works for you
In C32

Please Login or Register  to view this content.

Drag/Fill Down as required.

I tried Marcol's solution and it worked for all but item 1001, but that item is in ascending order and not descending like the others. Change up to all descending and make sure to give Marcol some rep for his solution.

Alan

It's not the sorting of the table that's the problem, it's the logic that is in question.

All except 1001 seem to have C1 as the lowest pricing level and A1 as the highest

Have a look at the workbook in post #5

I moved, and marked red what you have, and replaced it in yellow what I think it should be.

Am I wrong?

This is not easy to solve using lookup formulae, unless you fix all your data descending.

MATCH() will return values less than, exact match, and greater than, but the data must be sorted descending.

LOOKUP() and HLOOKUP() work with lists ascending, but will return the highest value that is less than the lookup value if no exact match is found.
You are asking for lowest value that is greater than the lookup value if no exact match is found.

One way around this is to use a formula to reverse the lookup list to descending when it is required to do so.

In H19

Please Login or Register  to view this content.

Drag Across to Column M then down as required.

We can now use this as an alternative lookup table when the original table row is ascending.
In Sheet1 C31 this mouthful

Please Login or Register  to view this content.

Drag/Fill Down

This might be easier to follow if we use named ranges, see Sheet "Names", these can be dynamic in your final worksheet.

Please Login or Register  to view this content.

I have added conditional formatting to highlight when the list is ascending and the alternative table is used for the lookups.

The extra header ascending row is only for reference to see the values returned, it is not requred for the formula.

I can't work out why you think the result for 1001 x 50 is C1, I think it should be B5.

Maybe one of the forums formula whizz-kids can shorten the formula a bit, but, unless I am missing something, I can't see how it can be done without sorting the data consistantly.

I am assuming you are using 2003 as your file suggests, you don't say what version you are using in your profile.

If you are using 2007 or above IFERROR() would simplify things.

Hope this helps.