Excel Index Match with Multiple Criteria and Multiple Match Types: Variant 2

Question

This question is similar to this one, but with different variable.

I want to extract the, "Stage" from data B using two criteria ("Date of Activity" and "Opportunity Name") from data A; The two criteria from data A will have different match types. The question I am trying to answer is, "At what Stage did an activity occur?" and believe some form of Index Match to be part of the answer.

If the two criteria from data A were both match type = exact, I know I could use array formula:

=MATCH(lookup_value_1&lookup_value_2, lookup_array_1&lookup_array_2, match_type).

Unfortunately, the "Date of Activity" needs to use the, "Less Than" match type and the "Opportunity Name" needs to use the, "Exact" match type.

Data A

Assigned|Date of Activity|Type of Activity|Opportunity Name
-----------------------------------------------------------
John    |11/15/2016      |CheckIn         |Ford
Peter   |11/15/2016      |Review          |Chevy

Data B

Last Modified|Opportunity Name|Stage
------------------------------------
11/1/2016    |Ford            |0
11/1/2016    |Chevy           |0
11/10/2016   |Ford            |1
11/10/2016   |Chevy           |1
11/20/2016   |Ford            |2
11/20/2016   |Chevy           |2
...

What is the expected result? If the date criterion is "less than", then there will be several matches, even combined with an exact match on Opp.Name. — teylyn, Feb 21 '17 at 22:50
The question I am trying to answer is, "At what Stage did an activity occur?". For example, John's, "CheckIn" activity occurred in Stage 2. From what I understand of Data B, in my use case, I am unable to know if there would be duplicates. However, if the dates are sorted oldest to newest, the way I understand the match function to work, it should be okay (but I would verify in the use case). — VLG, Feb 22 '17 at 03:57
Apologies... in the example, John's, "CheckIn" activity occurred in Stage 1 (not Stage 2) because 11/15 is less than 11/20. — VLG, Feb 22 '17 at 04:08
I *think* I see to what you are referring RE: multiple matches... but struggling with the formula in the first place to be able to verify. As I mentioned, the expected result example would be to return, "Stage 1" for the, "CheckIn" activity John did on 11/15/2016. — VLG, Feb 22 '17 at 05:25
Why is there such discrepancy between last modified and date of activity? Can't you sort out the data instead of trying to create an impossible formula? MATCH() with a 1 as the last parameter works with "less than or equal to", so getting only "less than" will not be achieved with Match(). I don't see a handle to ringfence the date range in Data B with just the parameter "less than x date" — teylyn, Feb 22 '17 at 07:42
I'm afraid this is real use case and it's proving to be more of a challenge than I thought. The "Date of Activity" can occur at any point in a given stage of an opportunity.... and the age of an opportunity stage will vary depending on the complexity. I have several rows of these data and I need to figure how many activities occurred at a given stage. — VLG, Feb 22 '17 at 17:00

teylyn · Accepted Answer · 2017-02-22T20:40:34.510

It can be done with a formula, using a match that ringfences one of the parameters into a range. This does not seem possible with the last modified date, but it can be done with the opportunity name. If the Data B table is sorted by Opportunity Name, then this formula will extract the stage for that opportunity name where the date of activity is less than or equal to the last modified date.

=INDEX(INDEX(Table1[Stage],MATCH(H2,Table1[Opportunity Name],0))
      :INDEX(Table1[Stage],MATCH(H2,Table1[Opportunity Name],1)),
       MATCH(F2,
            INDEX(Table1[Last Modified],MATCH(H2,Table1[Opportunity Name],0))
            :INDEX(Table1[Last Modified],MATCH(H2,Table1[Opportunity Name],1))))

The formula uses structured referencing for the table references, which helps with readability, but you can replace them with regular references, of course.

Again, the data table in columns A to C must be sorted by Opportunity name, ascending, for this to work.

Initial glance looks like, "success"! Note, the implementation also had side benefit of marking the output as #N/A if the, "Date of Activity" is less than any of the, "Last Modified" date (this means someone went in after-the-fact and logged an activity at an earlier date.... ). — VLG, Feb 22 '17 at 23:46

Excel Index Match with Multiple Criteria and Multiple Match Types: Variant 2

1 Answers1