I know this has been asked a number of times, but I haven't quite found an answer that fits my situation. I've tried using INDEX and the TRUE value, but it doesn't seem to working for me. I also tried changing the columns (DATE, then ITEM and COST), but to no avail.
I am using GOOGLE SHEETS.
The workbook has two sheets, this one is called Reference. The one it calls is called Sales. The data is UNSORTED by item, but is sorted by date, though duplicate instances may exist. I am using this formula:
=VLOOKUP(A2,Sales!A:C,3,false)
It works, but it calls the FIRST value. I need the LATEST value. It needs to go through the entire COLUMN to search for this value, then return the corresponding (latest) value on the third column. Both sheets have ITEM column; the purpose of Reference is to check if there is a match for the item existing on the Sales sheet and, if it's there, refer to the latest value "COST". Because numerous instances of ITEM and COST are in sales with differing values, I need to call up the latest number.
In addition, I have another formula that calls data from another sheet, in another workbook, that does virtually the same thing:
=VLOOKUP(A2,importrange("https://docs.google.com/spreadsheets/d/URL/","Sales!A:C"),3,false)
Again, it works at getting the value I need, just not the LATEST value. The sheet is formatted like this:
(SALES)
ITEM DATE COST
A2 B2 C2
A3 B3 C3
The reference sheet is like so:
(REFERENCE)
ITEM LOOKUP
A2 B2
A3 B3
Any help you can offer would be greatly appreciated.
all you need to do is sort it in descending order either based on dates or rows (based on your preferences / needs)
example:
=VLOOKUP(A2, SORT(Sales!A:C, 1 +N("1st column contains dates"), 0 +N("descending")), 3, )