Identify Value Farthest to Right

[whispyj]

Hi Everyone,

I am trying to figure out a way to get the latest date a which a customer has canceled a subscription. (Our customers come on and off fairly often.) Here's an example of what the data set looks like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]July-16[/TD]
[TD]Aug-16[/TD]
[TD]Sep-16[/TD]
[TD]Oct-16[/TD]
[TD]Nov-16[/TD]
[TD]Dec-16[/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]1[/TD]
[TD]-1[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

I want to pull the Date in row 1 for the column that has the last "-1" for each row. In other words, I want to get the latest date at which a customer has canceled, which is represented by a "-1."

Thank you in advance for your help!

 

[Jonmo1]

Assuming that table begins in A1 (The header 'Account' apears in A1)

Try
=LOOKUP(2,1/($B2:$J2=-1),$B$1:$J$1)

 

[whispyj]

That worked! Thanks Jonmo1. I'll have to learn more about the LOOKUP function.

 

[Jonmo1]

You're welcome