google-sheetsgoogle-sheets-formula

Returning the last input entry


ID Weight (kg) BMI Body-fat percentage organ fat
248 54.1 20.5 19.7 7
248 54.3 20.2 19.5 6
248 54.2 20.4 19.6 6

I have this google sheet formula:
=ARRAYFORMULA(IF(LEN(I3:I), IFERROR(VLOOKUP(I3:I, SORT(FILTER({B3:B, D3:D, E3:E, F3:F, G3:G}, LEN(B3:B)), 1, FALSE), {2, 3, 4, 5}, FALSE), ""), ""))

My formula returns this set of value:

ID Weight (kg) BMI Body-fat percentage organ fat
248 54.1 20.5 19.7 7

but this section:
SORT(FILTER({B3:B, D3:D, E3:E, F3:F, G3:G}, LEN(B3:B)), 1, TRUE)
didn't help to sort the order because IDs are the same. ChangingTRUE/FALSE won't make a difference.

Is it possible to get this set of value

ID Weight (kg) BMI Body-fat percentage organ fat
248 54.2 20.4 19.6 6

by changing my formula?

Here's my currently formula:

=ARRAYFORMULA(IF(LEN(I3:I), IFERROR(VLOOKUP(I3:I, SORT(FILTER({B3:B, D3:D, E3:E, F3:F, G3:G}, LEN(B3:B)), 1, FALSE), {2, 3, 4, 5}, FALSE), ""), ""))

I tried changing to TRUE, but it didn't make any differences because IDs are the same:
=ARRAYFORMULA(IF(LEN(I3:I), IFERROR(VLOOKUP(I3:I, SORT(FILTER({B3:B, D3:D, E3:E, F3:F, G3:G}, LEN(B3:B)), 1, TRUE), {2, 3, 4, 5}, FALSE), ""), ""))

then I tried MAX, but it only returns the value with the largest ID number:
=ARRAYFORMULA(IF(LEN(I3:I), IFERROR(VLOOKUP(I3:I, FILTER({B3:B, D3:D, E3:E, F3:F, G3:G}, ROW(B3:B) = MAX(FILTER(ROW(B3:B), B3:B = I3:I))), {2, 3, 4, 5}, FALSE), ""), ""))


Solution

  • You may try FILTER() and CHOOSEROWS() function.

    =MAP(TOCOL(I3:I,1),LAMBDA(x,CHOOSEROWS(FILTER(D:G,B:B=x),-1)))
    

    Using XLOOKUP() try-

    =MAP(TOCOL(I3:I,1),LAMBDA(x,XLOOKUP(x,B:B,D:G,"",,-1)))
    

    enter image description here