CODEWITHSUNDEEP
sortinggoogle-sheetsgoogle-sheets-formulavlookuplookup
Mirume
Mirume

Reputation: 73

How to get last value from VLookup?

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.

Upvotes: 2

Views: 11699

Answers (2)

Hila DG
Hila DG

Reputation: 728

By Definition, VLOOKUP would return the 1st value matching your search criteria, which leaves you with two options:
The Lazy one: Sort the other Sheet by Date Newest to Oldest (that what I would do)
Alternatively, use XLOOKUP

Upvotes: 0

player0
player0

Reputation: 1

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, )

Upvotes: 6

Related Questions