Search code examples
google-sheetsgoogle-sheets-formulaspreadsheet

Find the last value of a matching column in Google Spreadsheets

I'm trying to pull the last value of a matching column, but I'm finding increasingly issues and building a troubling long formula. Imagine I want to pull the last price for Fish.

So far I have

=MATCH(Crafts!A20,$1:$1,0) --> 3

Then

=INDEX($1:$1000,3,MATCH(Crafts!A20,$1:$1,0)) --> 25

This index formula is working when I give it a manual row. However, I don't know how to find the last value in that column so it returns 22.

COUNTA doesn't work as it ignores blanks. I could do COUNTA + COUNTBLANK, but they need a range, and I only have the MATCH, which returns a column index, not the column range.

Is there any easier way to achieve this?

Thanks.

Solution

  • I think I found the solution myself and it was indeed easier than expected, just needed a bit of a workaround.

    =LOOKUP(9^9,INDEX($1:$1001,,MATCH(Crafts!A20,$1:$1,0)))

    By removing the ROW index in the INDEX formula, it returns an array, the entire column. By performing a LOOKUP of a huge number, it just returns whatever the last value of the array is.

    Works, and it's cleaner than everything else I tried, by far.