Solved

Excel Show latest or lookup

Posted on 2006-07-18
1
179 Views
Last Modified: 2010-04-17
I got two tabs in excel:
TAB1 - contains unique record numbers
TAB2 - contains record data.. records are not in order.

In Tab1 I need a formula to show the record number's name and section with the latest date/time.


TAB1
record#   name   section
1             john         2
2             russ         1
3             ed            6
4
5

TAB2
record#   date               time               name    section  
1             1/5/2006        9:50 am         david        1        
1             1/5/2006        3:10 pm         john          2        
1             12/10/2005     11:00 pm       eve           1          
2              7/18/2006        2:00 pm       russ          1
2              7/18/2006        8:00 am       bill             2
3              5/15/2006        9:00 am       ken            3  
3              5/14/2006        8:00 am       liz              5
3              5/15/2006        9:15 am       ed              6
0
Comment
Question by:joberl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 6

Accepted Solution

by:
mwharff earned 500 total points
ID: 17137792
If the table in tab 1 starts at A1 and the table in Tab 2 starts in A1, you need to enter this as an array formula by using CTRL-Shift-Enter after typing the formula.

=MAX(--(A2=Tab2!$A$2:$A$9)*--(Tab2!$B$2:$B$9+Tab2!$C$2:$C$9))

Some other things to be aware of.

The date in column B and Time in Column C need to be in Excel date time values .  Integer for date and decimal for time.

Hope this helps.

Mike
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question