Find the last value of a matching column in Google Spreadsheets-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

Use:

=INDEX(INDIRECT("C"&MAX((ROW(C:C))*(C:C<>""))))

Update

Assuming cell Crafts!A20 = Fish - White and the desired output is in the same sheet as input Fish - White column then:

=INDEX(INDIRECT(SUBSTITUTE(
 ADDRESS(1,         MATCH(Crafts!A20, 1:1, )), "1", )&MAX(ROW(A:A))*(INDIRECT(
 ADDRESS(1,         MATCH(Crafts!A20, 1:1, ))&":"&
 ADDRESS(ROWS(A:A), MATCH(Crafts!A20, 1:1, )))<>"")))