I have 2 worksheets attached.
The 1st has an UNIT ID along with DATE, QTY SOLD, TOTAL $ Sold. I want another column showing UNIT COST.
This unit cost is found by looking at the UNIT ID and DATE from the 1st sheet, looking at the 2nd sheet and finding the UNIT COST of the item that is the most recent date.
So, in the worksheet 1, the 1st item on line 2, the answer should give me: $2.00.
Thanks
hi there. perhaps you can give more answers like you did for line 2 so that we can see a pattern. i'm guessing:
=LOOKUP(2,1/((SHEET2!$A$2:$A$11=A2)*(SHEET2!$B$2:$B$11<=B2)),SHEET2!$C$2:$C$11)
it would be better if you can convert Sheet2 range into a Table. just go to cell A1 of Sheet2 and press CTRL + T. press ok. that way, your range is dynamic. when it increases to 100 rows for eg, you don't have to change the formula
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
I’ll do that and get back. Could you explain what the formula you posted is doing, briefly, so I can get a better understanding of it? Thanks for the response. I’ll let you know when I get a chance to try it.
Re: FIND PRICE COST from MAX DATE based on ITEM ID and SALE DATE
Benishiryo - attached is the sheet with your requests. It appears, to me, that it is working flawlessly.
While you review my attachment, I will implement this over to my true data and see how it goes.
In the meantime, could you, or someone, explain how this formula you gave: =LOOKUP(2,1/((SHEET2!$A$2:$A$18=A2)*(SHEET2!$B$2:$B$18<=B2)),SHEET2!$C$2:$C$18)
is working?
Not understanding why the first 2 values of 2,1 after lookup are in there and if those would need to change at any time.
Thanks
Last edited by aetedford; 01-18-2018 at 09:54 AM.
Re: FIND PRICE COST from MAX DATE based on ITEM ID and SALE DATE
Wow - found the reference to the formula here: https://goo.gl/h5ZzCy
Great job.
Only issue I have is that I have millions of rows so this will take some time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks