Can I get MATCH to grab the last identical value in an array?

[CrankyPantz]

Hi everyone - I am wresting with some MATCH challenges and would appreciate some help.  In this case, my ideal outcome would be having MATCH grab the last value in a list of duplicate values (it defaults to grabbing the first duplicate value).  I have been trying other solutions such as MMULT but cannot crack this one.

Below is some sample data.  I have a database of Users who progressed through a game to specific Levels at a certain Time (the exact Time they reached that Level).  Consider the Time data here to be the time in minutes since the User started playing the game.

I have other data which contains the User and an event which happened at a particular Time.  I am trying to determine what Level the User was at during that Time.  The second section shows some sample inputs for User and Time, and the desired result for Level.

The last user in the source data is put at Level 10 because the MATCH formula is grabbing that row by mistake in some cases and I wanted to easily see when that was happening.

The right columns labeled X and Y show the results using two variations of the MATCH function.  column X uses the exact match argument ("0"); column Y uses "1" as the 3rd argument in the MATCH function.  Correct answers are highlighted in green.

Observations:

- In column X, the wrong answers are due to MATCH grabbing the first value in a list of duplicates.

- Column Y seems clearly to be the wrong approach but it yields some correct answers vs. column X.

- Row 13 is the same data as Row 3, but column Y yields different results.  Weird.

I'm looking for any help to yield the right results, even different formulas or macros.

Formula in cell I2 is: {=INDEX($A$2:$C$14,MATCH(1,(E2=$A$2:$A$14)*(F2>=$C$2:$C$14),0),2)}.  I used CSE when entering this formula, and copied it down to I14.

Formula in cell J2 is {=INDEX($A$2:$C$14,MATCH(1,(E2=$A$2:$A$14)*(F2>=$C$2:$C$14),1),2)}.  Ditto here for CSE and copied to J14.

Thanks in advance for help on this.

[#2]

@CrankyPantz 

When you want the last match, LOOKUP is the function to use.

I can reproduce your column G results with the following non-CSE formula:

=INDEX($B$2:$B$14,LOOKUP(1E+307,(ROW($A$2:$A$14)-ROW($A$2)+1)/((E2=$A$2:$A$14)*(F2>=$C$2:$C$14))))

[#3]

You are probably best off not reading the on-line help for LOOKUP. It will only mislead you.

LOOKUP has a number of useful properties:

1. Contrary to the on-line help, the lookup column does not need to be sorted in ascending order
2. If you search for a value so big (either in numeric magnitude or alphabetic sort order) it will always be last, LOOKUP matches the last value of the same data type as the first parameter
3. LOOKUP ignores error values
4. If you give LOOKUP two parameters, it returns the last match from the second parameter. If you give it three parameters, it returns the value in the third parameter that corresponds to the match found in the second parameter.
5. If you use LOOKUP in an array formula, it does not need to be array-entered. It's like SUMPRODUCT and AGGREGATE in that respect.

In the suggested formula, the magic happens in the second parameter of LOOKUP. The numerator is the index numbers 1 through 13, while the denominator is the results of your criteria testing.

The numerator of the second LOOKUP parameter returns index numbers 1 through 13. Although I could simplify the expression for the given layout, many people don't know how to change ROW($A$2:$A$14)-1 for a different worksheet layout, but have no trouble adjusting (ROW($A$2:$A$14)-ROW($A$2)+1).

The denominator is a Boolean expression holding the criteria for which values to include: a match for E2 in column A and F2>= values in column C. Each test returns an array of TRUE and FALSE, which get converted to 1 and 0 when used in an arithmetic expressions. Since the criteria are in the denominator, you get an array of either index number for the rows or a DIV/0! error values.

The first LOOKUP parameter 1E+307 is a very large number. Although the formula would work with the given layout using 14, I like to choose a number so large it will always work. Doing so gets people's attention and makes them reluctant to make changes.

Finally, rather than applying INDEX to $A$2:$C$14 and then specifying the result come from the second column, I just use $B$2:$B$14 and drop the third parameter in the INDEX function.

[#4 · Accepted solution]

@CrankyPantz 

Your desired results can be returned by solely using LOOKUP, like this starting in Row 2: 

=LOOKUP(2,

1/((A$2:A$14=E2)*(C$2:C$14<=F2)),

B$2:B$14)

[#5]

@Twifoo,

The simple LOOKUP is so obvious in hindsight. Thanks for jumping in!

Brad

[#6]

You’re welcome.

[#7]

@Brad_Yundt 

@Twifoo 

Thanks so much to both of you for your help.  LOOKUP does exactly what I needed.

[#8]

You’re very much welcome. By the way, LOOKUP is my favorite function.