View original (French)

How to find non-empty cell

Solved
flexi2202 Posted messages 3640 Registration date   Status Member Last intervention   -  
flexi2202 Posted messages 3640 Registration date   Status Member Last intervention   -
Good evening everyone
I am trying to get Excel to display the last value in row 1
I tried this formula but I don't know how to use it on row 1, as I am getting the value 0
=LOOKUP(9^9;1:1)

What should I do
Thank you for your help

5 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    if you only have numeric values the formula is correct
    =SEARCH(9^9;1:1)

    if you only have text formatted values the formula is
    =SEARCH("zzzz";1:1)

    if you have both text and numeric values you need to use an array formula confirmed with Ctrl, Shift and Enter
    =INDEX(A1:O1;MAX(NOT(ISBLANK(A1:O1))*COLUMN(A1:O1)))

    if you have no empty cells between the values
    =INDEX(1:1;COUNTA(1:1))

    or
    =INDIRECT(ADDRESS(1;MAX(MATCH(9^9;1:1);MATCH("zzzz";1:1));4))

    Cheers
    Mike-31

    I am responsible for what I say, not for what you understand...
    1