I’m using Google Sheets and would like to get the last value in column A when it is filtered based on the values in column B that match the value 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)
However, I have found an alternative that is more concise, but I do not understand how it works:
=ArrayFormula(LOOKUP(2,1/($B:$B=$D$1),$A:$A))
This formula uses the LOOKUP function to find the last value in column A that corresponds to the specified value in cell D1, when column B is filtered based on that value.
The ArrayFormula wrapper allows the formula to apply to the entire column instead of just one cell.
The LOOKUP function searches for the specified value (2 in this case) in an array and returns the value in the corresponding position in another array.
In this case, the 1/($B:$B=$D$1) part creates an array of 1s and 0s, where the value is 1 if the corresponding cell in column B matches the value in cell D1, and 0 otherwise.
The LOOKUP function searches for the value 2 in this array, which will always return the last 1 in the array (since there are no values greater than 1).
Finally, the LOOKUP function returns the value in the corresponding position in column A.