Solved

Excel Show latest or lookup

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …

735 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