return the date that value

Copper Contributor

Hallo, I need the hightlight column to return the date that value is filled in

Mascarello_0-1642633066602.png

 

 

5 Replies

@Mascarello 

 

Hello, hope this formula works.

 

=INDEX($B$1:$M$1,1,MATCH(TRUE,B2:M2>0,0))

 

alannavarro_0-1642640461348.png

 

Didnt work@alannavarro 

Mascarello_0-1642718920492.png

 

@Mascarello 

@alannavarro 's solution is broadly correct.  Using a different version of Excel, my solutions look somewhat different, but are broadly similar in approach

= BYROW(amount,
    LAMBDA(a,XLOOKUP(TRUE,ISNUMBER(a),date,""))
  )


= XLOOKUP(
    TRUE,
    ISNUMBER(INDEX(amount,@k,)),
    date,
    "")

What I have done, since you report that the >0 test does not work, is assume that the apparent blank cells in fact have text content.  The ISNUMBER function works in that situation, though a 0 would then cause a wrong result.

On the blank cells I have a formula to calculate the value.And I dont have able the functions, Byrow and Lambda. And also I cant enable these functions. I took out the formulas of the blank cell, and the @alannavarro´s solution works correctly.

I am glad your problem is sorted. The two solutions I produced are for Excel 365 insider and Excel 2019 respectively. I need a lot of practice in order to use the new functionality to its maximum effectiveness.
My formulas were intended to work despite the apparent blanks having content but, since you are able to clear them out, that will do the job.