Index(Match() not returning right value

Question

Hello,

I'm creating a personal budget and analysis workbook. I'm trying to return the last date that I made a certain category of purchase using Index() and Match(). I only update the workbook once a month, so I don't want to use the TODAY() function, since it'll skew any averages I generate.

Data is arranged like this (with sample data):

Date Title Category Amount

1/1/13 CVS Healthcare -$10

3/1/13 BP Transportation -$40

3/15/13 Mayo Healthcare -$50

4/1/13 ComEd Utilities -$30

4/15/13 CVS Healthcare -$20

The array is sorted in ascending order by Date.

I have no problem returning the first instance of a certain category (such as "Healthcare"). This is the formula I'm using to return the date of the first instance: =INDEX(AllBudget!$A:$C, MATCH("Healthcare", AllBudget!$C:$C, 0), 1).

i.e. this function always returns 1/1/13

However, whenever I try to return the last instance, something goes wrong with the MATCH() function and it returns the wrong value. This is the current formula I am using: =INDEX(AllBudget!$A:$C, MATCH("Healthcare", AllBudget!$C:$C, 1), 1). It returns the date in row 321, which is when "Healthcare" appears, but the last instance of "Healthcare" right now is row 800.

i.e. this function always returns 3/15/13, when it should return 4/15/13

I've also tried to use Offset() and Match(), with the same result.

I've never used these functions before today, so maybe I'm just doing something wrong. Any help would be great!

Answer 1

You can use an array formula for this. Array formulas must be confirmed with Ctrl+Shift+Enter instead of just Enter.

=INDEX(AllBudget!$A$1:$A$1000,MAX(ROW($1:$1000)*(AllBudget!$C$1:$C$1000="Healthcare")))

The result will most likely be a number; simply format it as a date to see the desired result.

You can adjust the ranges as needed, but I wouldn't recommend using entire columns; that would be slow.

Answer 2

Hi,

Try this

1. Suppose your data is in range A2:D6.  Headings are in A1:D1
2. In cell A11, type Healthcare
3. In cell D11, enter this formula

=LOOKUP(2,1/($C$2:$C$6=A11),$A$2:$A$6)

Hope this helps.

Answer 3

Works like a charm! Thank you so much! I'll be researching array formulas now.