Returning the last value from the FILTER function in google sheets

3
votes

I'm using Google Sheets and would like to get the last value in column when it is filtered based on the values in a separate column as shown in the screenshot:

Screenshot of example spreadsheet

I'd like to get the last value from column A, where the value in column B matches that specified in cell D1.

I've managed to do this with the following:

=INDEX(FILTER($A:$A,$B,$B=$D$1),COUNTA(FILTER($A:$A,$B:$B-$D$1)),1)

This works but it seems unnecessary to have the second FILTER and COUNTA as it makes it harder to understand. Is there no way I can just return the last value from the FILTER function?

Since posting this I've found another way that's more concise, but I have to confess I don't actually understand how it works:

=ArrayFormula(LOOKUP(2,1/($B:$B=$D$1),$A:$A))

2
google-sheets-formula
Wish you have been posted it as an answer. (it took me a while to find what you did) – M at

2 Answers

3
votes

Here you will know about sort and array_Constrain:

=array_constrain(filter(sort({A1:A,row(A1:A)},2,false),B1:B=D1),1,1)

or you can use query:

=query(filter({A1:A,row(A1:A)},B1:B=D1),"Select Col1 order by Col2 desc limit 1")

or you can use indirect:

=indirect("A" & max(filter(row(A:A),B:B=D1)))
1
votes

I know this is a way that I do that sometimes. it takes advantage of the VLOOKUP(....TRUE) [default] option.

=VLOOKUP(9^99,FILTER({ROW(A:A),A:A},B:B=D1),2)