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), ""), ""))
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)))