Multiple Steps/Criteria with INDEX/MATCH

MattM09 said:

Hey All,

I'm trying to lookup the current status of a subscription in a database if a user has renewed at some point in the past. Each input has an ID, and its chronological. So, in theory, you should be able to lookup the most recent input ID that matches to the renewed customer's subscriber ID.

I tried:

=index($g$2:$g$115744,match($c$7&(IF(SEARCH($C$7,$C$2:$C$115774,0),MAX($A$2:$A$115744),0),$C$2:$C$115744&$A$2:$A$115744,0))

So basically,

1. You have a subscriber ID with multiple entries
2. What is the highest input ID of that given subscriber ID
3. What is the status code of the highest input ID of that given subscriber ID

I know I'm missing something, but not sure what.

Thanks!

Click to expand...

MattM09 said:

Thanks for the quick reply. Here is a sample:

ID	date	subscriber_id	event	attribute	value type	value_int	new status	is status
1	4/9/12	1234	renewed	order_id	int	1234
2	4/9/12	1234	change	status	int	1
3	5/18/12	1234	change	status	int	-1

<TBODY>
</TBODY>

in this scenario, the subscriber 1234 renewed their subscription on 4/9. The system logged that a change was made to active (1) and then in May, they canceled their subscription. I'm trying to produce a formula to go in "Is Status" to say what the current, active status of that subscriber is. I have a formula for anything that isn't a renew event, but am having a hard time figuring that one out. I want it to just say what the value_it is for 5/18/12 as that is the last logged event of that subscriber, and consequently the current status.

Thanks!

Click to expand...

MattM09 said:

Find the most recent ID (events can happen on the same day, so you have to go by the ID, not the date) for a subscriber and return the corresponding entry from the "value_int" column

Click to expand...

MattM09 said:

could you explain how that works?

Click to expand...

T. Valko said:

I get asked that question often so I wrote a "generic" explanation once and refer to it when asked the question. Here's that explanation:

Let's use this example to demonstrate how this works:

..........A.............B
1.....header.....header
2........9.............10
3........7.............12
4........9.............15
5........5.............16

Return the value in column B that corresponds to the *last instance* of 9 in column A.

=LOOKUP(2,1/(A2:A5=9),B2:B5)

This expression will return an array of either TRUE or FALSE:

(A2:A5=9)

A2 = 9 = 9 = TRUE
A3 = 7 = 9 = FALSE
A4 = 9 = 9 = TRUE
A5 = 5 = 9 = FALSE

We then use the divison operation to coerce those logical values to numbers:

A2 = 1 / TRUE = 1
A3 = 1 / FALSE = #DIV/0!
A4 = 1 / TRUE = 1
A5 = 1 / FALSE = #DIV/0!

At this point the formula looks like this:

=LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5)

The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value.

The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the result of this expression:

1/(A2:A5=9)

will not return a value greater than 1.

This is how that would look:

..........A.............B
1.....header.....header
2........1.............10
3...#DIV/0!.......12
4........1.............15
5...#DIV/0!.......16

So, the *last instance* of 9 was in A4. Return the corresponding value from B4.

=LOOKUP(2,1/(A2:A5=9),B2:B5) = 15

Click to expand...

MattM09 said:

This is very helpful, however the data needs to be re-sorted for another function which would then make this not work. Is there another way? Could I just lock the cells after the original computation perhaps?

Click to expand...