Thread: Returning Value Based on Changing Date

Ok here’s my dilemma. I have a table listing various entries based on name, amount and date of entry. In a separate table I would like to request the newest value entered for a name (maybe the newest and the one before the newest as well). So in table A I have categories X, Y and Z: X is a name, Y is an amount, and Z is the date of transaction. Table A is simply a running record sorted by newest entry to oldest entry. In Table B I have categories X, S and T: X is again a name, S is the newest/most-recent entry (i.e. Y for the amount) made by X in Table A, and T is the penultimate (i.e. Y-1) entry made by X in Table A. I’m fairly new to this excel thing, but I’m sure there’s an easy fix for this. Please help.

Maybe you could post a sample attachment with your setup on the results you would like then I think you could get more help.

To keep things simple let's create some dummy ranges...

Let's assume "TableA" is on sheet1 and X is Col A, Y is Col B and Z is Col C
Let's assume "TableB" is on sheet2 and X is Col A, S is Col B and T is Col C

For ease of calculation let's add a column to "TableB"

Sheet2!D2: =MATCH($A2,Sheet1!$A$1:$A$1000,0)

The above (let's say this is "R") returns the first row on Sheet1 that contains the name of interest... given the data on Sheet1 is sorted newest to oldest this row contains "S"

Sheet2!B2: =VLOOKUP($A2,Sheet1!$A$1:$C$1000,3,0)

Given we know the row in which "S" appeared ("R") we can repeat the process for "T" but this time we adjust the range to exclude all rows up to and including row "R"
so we find the first match in the remaining range - ie the 2nd record

Sheet2!C2: =VLOOKUP($A2,INDEX(Sheet1!$A:$A,$D2+1):Sheet1!$C$1000,3,0)

Does that help ?