I want to lookup the purchase records for a list of items having one quantity each from a data having following fields column a,b,c,d,e as follows item code, quantity, date, invoice number, discount in such a way that it uses the lifo principle but based on the quantity of purchase records for eg item a is purchase twice one with 2 qty and another with 1 qty on different date under different invoice number now the list of items contains item a at three different instances with one quantity each I want to lookup the purchase records for all three quantities based on lifo details.
For eg So basically let us say i have an item code for a book "9780000000001" which is unique but this item code has been purchased by me from my two suppliers from supplier one 2 qty on 01-12-2022 and another 1 qty from 2nd supplier on 01-01-2023, now suppose i have a list of items in vertical order of excel cells wherein i have the itemcode "9780000000001" in cell number G2, G4 and G5 all having 1 qty so from the data of purchase records I want to lookup this item code based on lifo principle i.e. last in first out so against cell G2 row i want the purchase record of 2nd supplier to be matched here i.e. supplier name, purchase price, invoice number, invoice date and purchase discount and now in cell G4 row i want data to be mapped from 1st supplier with it details and when we move down to G5 cell row again data from 1st supplier needs to be mapped
Used various vlookup formulas including countif if scenarios using chatgpt index match etc. But not getting desired result.
