CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Bryan Hurst
Bryan Hurst

Reputation: 95

Google Sheets Lookup Row and Get Last Value

I have a Google Sheet with two sheets in it.

The first sheet is a list of songs with Title, Library #, Composer, ..., Last Performance Date
The second sheet is a list of performance dates with Title, Library #, date 1, date 2, ..., date n

Sheet 1 Example:

Title,     Library #,    Composer,    Last Performance Date
Hip Song   1007          David        {formula}
Slow Song  1002          Bob          {formula}
Other Song 1004          David        {formula}

Sheet 2 Example:

Title,     Library #,    Dates ->
Slow Song  1002          2021-01-12    2021-02-15
Other Song 1004          
Hip Song   1007          2021-01-05

How can I automatically fill in the "Last Performance Date" column on the first sheet by looking up the song on the second sheet by the "Library #" and getting the last value in that row (with a variable number of dates per song on the second sheet).

With the example above, the Last Performance Date for "Hip Song" would be "2021-01-05", "Slow Song" would be "2021-02-15", and "Other Song" would be "" (an empty string or nothing).
For any given song, there could be no dates on the second sheet or a 100+ dates on that song's row.

Upvotes: 0

Views: 1642

Answers (2)

player0
player0

Reputation: 1

if your dates (if present) are increasing with each next column try:

=INDEX(IF(B2:B="",,IFERROR(1*SPLIT(FLATTEN(QUERY(TRANSPOSE(IFNA(VLOOKUP(B2:B, Sheet2!B1:1000, 
 TRANSPOSE(SORT(SEQUENCE(COLUMNS(Sheet2!C1:1))+1, 1, 0))), 0)),,9^9)), " "))),,1)

enter image description here

demo spreadsheet

fx transcript: count columns from column with 1st date and create a sequence of number with +1 offset for each number. then sort the sequence in descending order and transpose it into row - this will be the 3rd parameter of vlookup which will return date columns in reverse order eg.: ..., Col5, Col4, Col3, Col2. so in vlookup we look for B2:B values in range Sheet2:B1:1000 and return multiple columns on each match. next, we transpose all results of vlookup and with query parameter ,,9^9 we collapse all rows of each column into a single row. next, we flatten the results and split by empty space (which was inserted after each date via query). in this stage, column with the latest date became our 1st column. next, dates are numeric values so we multiply them by 1 to catch errors of those cells that have no date only empty spaces. at last, we use simple if statement to check if B2:B range is empty. if so we return no value, otherwise we return date. and to limit the output only to 1 single column we use index to return all rows but only the first column

Upvotes: 1

Andrew Stegmaier
Andrew Stegmaier

Reputation: 3787

I'd add a helper column to Sheet2 that contains the formula that calculates the "latest" date for a given song:

Sheet2

A B C D E
1 Title Library # Latest Date Dates
2 Slow Song 1002 =IF(MAX(D2:E2)=0,"",MAX(D2:E2)) 2021-01-12 2021-02-15
3 Other Song 1004 =IF(MAX(D3:E3)=0,"",MAX(D3:E3))
4 Hip Song 1007 =IF(MAX(D4:E4)=0,"",MAX(D4:E4)) 2021-01-05

The IF(X=0,"",X) part of the formula is so that songs that don't have any dates show up as blank instead of 0 (which will get formatted as 1899-12-30).

Then, in Sheet1, use the VLOOKUP function to find the matching "latest date":

Sheet1

A B C D
1 Title Library # Composer Last Performance Date
2 Hip Song 1007 David =VLOOKUP(B2,Sheet2!$B$2:$C$4,2,FALSE)
3 Slow Song 1002 Bob =VLOOKUP(B3,Sheet2!$B$2:$C$4,2,FALSE)
4 Other Song 1004 David =VLOOKUP(B4,Sheet2!$B$2:$C$4,2,FALSE)

Note the FALSE as the last parameter to VLOOKUP, which makes sure it will still return correct results, even if the data in Sheet2 is not sorted by Library #.

See this example spreadsheet.

Upvotes: 1

Related Questions