Forum Discussion
Can I get MATCH to grab the last identical value in an array?
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)
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))))
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:
- Contrary to the on-line help, the lookup column does not need to be sorted in ascending order
- 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
- LOOKUP ignores error values
- 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.
- 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.