Lookup value based on latest matching criteria

Question

Below is an example of a table I have, what I am trying to do is get the value in the value column for a specific criteria based on the last occurrence (not including today's date).

So in the example below I want to find the value for the last occurrence of 'A', which is 12.

I think this can be done using an Index-Match, I just can't get my head around it though.

For example

Todays Date: 15/12/2013
---------------------------------|
|Date        | Criteria | Value
|--------------------------------|
|12/11/2013  | A        | 3      |
|16/11/2013  | B        | 6      |
|27/11/2013  | C        | 7      |
|3/12/2013   | A        | 12     |
|5/12/2013   | B        | 8      |
|15/12/2013  | A        |        |
----------------------------------

EDIT: I would also like to add that this formula will be in a different sheet to the table above. The sheet reference in the formula also needs to be dynamic, it will draw the sheet name from another cell.

Answer 1

I would use this formula:

=index(C:C,max(arrayformula(match(filter(A:A,B:B="A",C:C<>""),A:A,0))),1)

This formula assumes that your data is in the columns A,B,C and for every "A" value in the Criteria column, the Date is different. (If that's not the case, then this formula won't work, see below.

Let's look the formula inside from outside:

1. filter(A:A,B:B="A",C:C<>"") - This will result with the dates where there is an "A" in the Criteria column, and where the Value column is not empty.
2. arrayformula(match(filter(A:A,B:B="A",C:C<>""),A:A,0)) - In this step we basically find the row number in which those dates are present. The match function will search for the dates (counted in step 1). The arrayformula is needed because there will be more results.
3. max(arrayformula(match(filter(A:A,B:B="A",C:C<>""),A:A,0))) - This will find the maximum row number (The maximum row number which contains an "A" in the Criteria column)
4. index(C:C,max(arrayformula(match(filter(A:A,B:B="A",C:C<>""),A:A,0))),1) - Finally, we use the INDEX function to navigate to the value, which has the maximum row number.

Now, if you want this formula to work on another sheet, you should write, instead of for example:

=index(C:C,... => =index(Data!C:C,...

Assuming that your data is in your Data worksheet.

If you want to this sheet to be dynamic, it's a bit tricky. Let's assume, that you're getting the value of the sheet name from the G1 cell. Then you should write:

=index(indirect(concatenate(G1,"!C:C")),...

This is not so pretty as you should do this for every occasion when it occurs in that long formula (described earlier). Instead you can do some pre-work.

Let's write this to your H1 cell: =concatenate(G1,"!C:C") - If in the G1 cell the sheet name is "Data", then the H1 cell should contain: Data!C:C, similarly you can add to the

H2 cell: =concatenate(G1,"!A:A"), H3 cell: =concatenate(G1,"!B:B")

Now you can write (and that's the final answer for your question I think):

=index(indirect(H1),max(arrayformula(match(filter(indirect(H2),indirect(H3)="A",indirect(H1)<>""),indirect(H2),0))),1) - where H1,H2,H3 will reference to your Data sheet's columns.

I hope it helps.

Answer 2

Use the following formula to accomplish that.

Formula

=QUERY(
   B1:D6,                // data
   "SELECT D             // select
    WHERE                // where clause
      C = 'A' AND        // first criterium
      D IS NOT NULL      // second criterium
    ORDER BY B DESC      // order by
    LIMIT 1,             // limit
    0"                   // headers
 )

for copy/paste
=QUERY(B1:D6, "SELECT D WHERE C = 'A' AND D IS NOT NULL ORDER BY B DESC LIMIT 1", 0)

Explained

The clue to the formula is the usage of the ORDER BY and the LIMIT options within the QUERY formula. The WHERE clauses will prepare the result in the first place. Next, column B (the dates) is ordered descendingly (highest first). The LIMIT option sets the amount of rows to be displayed at 1.

Example

I've created an example file for you: Lookup value based on latest matching Criteria