Find last value in column

Question

In my Google Sheet I need to get the number in the last populated cell between J2 and J32. A new Cell is populated almost daily. For example here I need to get J27:

So far I have been doing it with 31 IF statements:

=IF(
J32 = "",
IF(
    J31 = "",
    IF(
        J30 = "",
        IF(
            J29 = "",
            IF(
                J28 = "",
                IF(
                    J27 = "",
                    IF(
                        J26 = "",
                        IF(
                            J25 = "",
                            IF(
                                J24 = "",
                                IF(
                                    J23 = "",
                                    IF(
                                        J22 = "",
                                        IF(
                                            J21 = "",
                                            IF(
                                                J20 = "",
                                                IF(
                                                    J19 = "",
                                                    IF(
                                                        J18 = "",
                                                        IF(
                                                            J17 = "",
                                                            IF(
                                                                J16 = "",
                                                                IF(
                                                                    J15 = "",
                                                                    IF(
                                                                        J14 = "",
                                                                        IF(
                                                                            J13 = "",
                                                                            IF(
                                                                                J12 = "",
                                                                                IF(
                                                                                    J11 = "",
                                                                                    IF(
                                                                                        J10 = "",
                                                                                        IF(
                                                                                            J9 = "",
                                                                                            IF(
                                                                                                J8 = "",
                                                                                                IF(
                                                                                                    J7 = "",
                                                                                                    IF(
                                                                                                        J6 = "",
                                                                                                        IF(
                                                                                                            J5 = "",
                                                                                                            IF(
                                                                                                                J4 = "",
                                                                                                                IF(
                                                                                                                    J3 = "",
                                                                                                                    IF(
                                                                                                                        J2 = "",
                                                                                                                        "",
                                                                                                                        J2
                                                                                                                    ),
                                                                                                                    J3
                                                                                                                ),
                                                                                                                J4
                                                                                                            ),
                                                                                                            J5
                                                                                                        ),
                                                                                                        J6
                                                                                                    ),
                                                                                                    J7
                                                                                                ),
                                                                                                J8
                                                                                            ),
                                                                                            J9
                                                                                        ),
                                                                                        J10
                                                                                    ),
                                                                                    J11
                                                                                ),
                                                                                J12
                                                                            ),
                                                                            J13
                                                                        ),
                                                                        J14
                                                                    ),
                                                                    J15
                                                                ),
                                                                J16
                                                            ),
                                                            J17
                                                        ),
                                                        J18
                                                    ),
                                                    J19
                                                ),
                                                J20
                                            ),
                                            J21
                                        ),
                                        J22
                                    ),
                                    J23
                                ),
                                J24
                            ),
                            J25
                        ),
                        J26
                    ),
                    J27
                ),
                J28
            ),
            J29
        ),
        J30
    ),
    J31
),
J32
) - Gesamt!Y$34

I have the suspicion that it's not giving me the correct results, as some numbers are off sometimes. Is there a better way to do this?

Answer 1

I suggest:

=vlookup(1E+100,J2:J32,1)  

though this could give an incorrect answer if any of the numbers in J2:J32 are as big or bigger than:

10,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.

The assumption is that between J2 and J32 includes both J2 and J32 in the search range.

Answer 2

you can use the formula: =INDEX(J2:J32, MATCH(MAX(J2:J32),J2:J32, 1))

Basically, it reads as find the value of the item at the position of the last match for a number less than or equal to the largest number in the range J2:J32.

INDEX: https://support.google.com/docs/answer/3098242?hl=en&ref_topic=3105472 MATCH: https://support.google.com/docs/answer/3093378