Index Returning wrong value in certain cells

[bobredford]

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:

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?

[Tony Valko]

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

I didn't download your file.

Try replacing the 1 with 0.

[Richard Buttrey]

Hi, and welcome to the forum


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

Please Login or Register  to view this content.

[Glenn Kennedy]

... or using a non-volatile formula:

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

[bobredford]

I didn't download your file.

Try replacing the 1 with 0.

I did try messing with that value, but using 0 matches the earliest date, rather than the latest.
This one on the other hand seems consistent if you're doing that.

Hi, and welcome to the forum


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

Please Login or Register  to view this content.

Thank you! I hope I can contribute in the future :D

And thank you for the help! This worked perfectly!

... or using a non-volatile formula:

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

Wow! This one works perfectly too! Guess I need to learn the capabilities of each formula more.


Thank you, all! I really appreciate this! You are awesome

[Glenn Kennedy]

You're welcome.