Solved

Excel Show latest or lookup

Posted on 2006-07-18
1
175 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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.
A short article about problems I had with the new location API and permissions in Marshmallow
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

864 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now