Hi Excel Forum Community! I just joined and this is my first post (and my reason for officially joining!). I have been trying to figure out a formula to reconcile two spreadsheets: One source worksheet (named 'products') with product that should be shipped back to us with a second spreadsheet (named 'received') that contains what we actually received.
Spreadsheet 'products' has the following columns:
return-date: The date the product was "ordered" and in queue to be sent to me
sku: The identifying unit/title for each product - these are usually duplicated, which is where my problem lies
quantity: quantity
serial-number: Unique serial for each sku
Spreadsheet 'received' has the following columns:
order-date: The date the sku was added to an order to be shipped back
order-id: The order the sku was added to
shipment-date: The date the sku was shipped back
sku: The identifying unit/title for each product - these are usually duplicated, which is where my problem lies
quantity: quantity
received-date: The date I actually received it back
serial-number: The serial number I captured when the product arrived
I need to add a column to 'products' that checks if that specific line was received or at least shipped back if not received. I was able to write a VLOOKUP/IFERROR formula that checked for the serial being present on the 'received' worksheet. That part is easy. But when I got to the products that were not yet received, checking to see if they were sent is where I ran into issues. Since the products are duplicated, my formula was checking to see if any of the unreceived product from 'products' has been shipped back and would not account for products already assigned to a previous line from 'products'.
TL;DR: I am expecting 10 of Product A. Two units have been received and match serials. Of the 8 Product A unaccounted for, even though only 5 others have been sent back, my formula returns a value for all 8.
Current Formula: =IFERROR((VLOOKUP(D2,received!G:G,1,FALSE)),IFERROR(VLOOKUP(B2,received!D:D,1,FALSE),"Not Found"))
Bookmarks