Index Returning wrong value in certain cells

Hi all,

I'm looking at this excel sheet our company has had for awhile, and we're unsure how this formula worked.
I've researched for days and I'm clearly not experienced.

We have these month columns with values 0-1, and we want the "End Date" column to scan the array and find the latest column with anything put into it.

IE:
Latest value is "1" on "18-March" for the person in Row 7.
So we'd want the "End Date" to reflect "18-March"

The formula:

Formula:

Please Login or Register  to view this content.

I've attached an example below where you can see that Row 15 is drawing the wrong value.

How would I go about doing this?

Originally Posted by bobredford

=INDEX(P$5:AY$5,MATCH(TRUE,INDEX(P6:AY6<>0,),1))

I didn't download your file.

Try replacing the 1 with 0.

Hi, and welcome to the forum


I'd use the following in N15, copied up to N6

Formula:

Please Login or Register  to view this content.

... or using a non-volatile formula:

=IFERROR(LOOKUP(2,1/(P6:BA6<>""),$P$5:$BA$5),"")

You're welcome.