Complex lookup (complex for me...)

Hello,

I have come stuck on trying to find a solution to the problem below and would appreciate any help required.

I am trying to lookup an item number from row E, in this example E1 (number 3767477) in Sheet 1 and find it in Sheet 2, and then once I have found the item number in sheet 2, I want to find in sheet 2 column 8, the quantity that is closest (greater than) to the quantity in sheet 1 - I1, and then return the value from column 9 in sheet 2 and return this into sheet 1 column L. So in the example below the solution would find B8 in sheet 2 and return B9 from sheet 2 into L1 in sheet 1.

From this I want to be able to use this for multiple products and quantities and return the value for each in column L.

5-28-2014 10-12-54 AM.jpg

This is probably an easy fix but I have been scratching my head for a while now and can’t get a combination of formulas to work.

Thanks in advance,

Ryan

Posting a better example would help. From what I see you have no answer in column L and I do not understand what you are saying based on the picture. All of the item numbers in column 1 on sheet 2 are the exact same, how do you know which one to use?

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)