I need serious help as I have tried all kinds of different formulas and nothing has worked. I have three sheets of data. The first sheet (we'll call sheet1) is a receipt record with Part Number, Supplier Number, Date, Qty Received. This is where the formula resides that I am having issues with figuring out. Sheet2 has a list of rejected parts, with Part Number, Supplier Number, Date, Qty Rejected. I am trying to extract the Rejected quantities from sheet2 and place them on sheet1. I created a UID by Concatenating Part Number, Supplier Number and Date in a column in both sheets. I can easily pick up the data where the receipts in sheet one match the rejects in sheet2 by using Sumif formula. Now to the difficult part. Not all parts rejected are going to match the date received so where these do not match I need to find the closest previous receipt record Sheet1 I can match it with. I cant use where data is 0 or not found then match using part number because then it will just pull everything twice. I could spend hours with a mile long post on everything I have tried. Rather than do that I will keep it short and listen for your suggestions. There is one solution and I know the first one most will want to post here which may work but involves an array formula Cntrl+Shift+Enter and that would slow the work book down dramatically.
