-1

I have a spreadsheet where dates are being recorded in regards to individuals, with additional data, as such:

Tom   |   xyz   |   5/2/2012  
Dick  |   foo   |   5/2/2012  
Tom   |   bar   |   6/1/2012

On another sheet there is a line in which I want to be able to put in the name, such as Tom, and retrieve on the following cell through a formula the data for the LAST (most recent by date) entry in the first sheet. So the first sheet is a log, and the second sheet displays the most recent one. In the following example, the first cell is entered and the remaining are formulas displaying data from the first sheet:

Tom   |   bar   |   6/1/2012

and so on, showing the latest dated entry in the log.

I'm stumped, any ideas?

Community
  • 1
  • 1
Joshua
  • 15
  • 5

1 Answers1

0

If you only need to do a single lookup, you can do that by adding two new columns in your log sheet:

Sheet1

  | A    | B   | C        | D | E | F
1 | Tom  | xyz | 6/2/2012 |   | * | *
2 | Dick | foo | 5/2/2012 |   | * | *
3 | Tom  | bar | 6/1/2012 |   | * | *

Sheet2

  | A   | B          | C
1 | Tom | =Sheet1.E1 | =Sheet1.F1

*(E1) = =IF(AND($A1=Sheet2.$A$1;E2=0);B1;E2)

(i.e. paste the formula above in E1, then copy/paste it in the other cells with *)

Explanation: if A is not what you're looking for, go for the next; if it is, but there is a non-empty next, go for the next; otherwise, get it. This way you're selecting the last one corresponding to your search. I'm assuming you want the last entry, not "the one with the most recent date", since that's what you asked in your example. If I interpreted your question wrong, please update it and I can try to provide a better answer.

Update: If the log dates can be out of order, here's how you get the last entry:

*(F1) = =IF(AND($A1=Sheet2.$A$1;C1>=F2);C1;F2)

*(E1) = =IF(C1=F1;B1;E2)

Here I just replaced the test F2=0 (select next if non-empty) for C1>=F2 (select next if more recent) and, for the other column, select next if the first test also did so.

Disclaimer: I'm very inexperienced with spreadsheets, the solution above is ugly but gets the job done. For instance, if you wanted a 2nd row in Sheet2 to do another lookup, you'd need to add two more columns to Sheet1, etc.

mgibsonbr
  • 21,755
  • 7
  • 70
  • 112
  • It is the MOST RECENT DATE that I am looking to get. And the number of entries will continue to increase over time, and I want to fetch only the latest date one, closest to the current date. – Joshua Jun 12 '12 at 05:09
  • Your example is showing `Tom | bar | 6/1/2012`, which is NOT the last date. So please clarify: as new entries are added to the log, will they appear in the end always (i.e. will they be "naturally ordered") or not? – mgibsonbr Jun 12 '12 at 05:13
  • Answer updated. And in case I wasn't clear, the idea is to repeat the forumla (copy/pasted, which updates the indices) for every new entry added (now or in the future). BTW the extra columns don't need to be in Sheet1, they can perfecly be in Sheet2 (or somewhere else) if you need to. But AFAIK you'd always need to "pre-allocate" the forumlas, there's no general solution that auto-expands as new rows are added (even if you used an interval, you'd have to specify an end to it). I might be mistaken, though (see my disclaimer). – mgibsonbr Jun 12 '12 at 05:29