Find Data from oldest date

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Hi, I have had a look around online but can't seem to find what I want.

I have devices in one column and dates received in another. I do not wish to reorder the column as they are set up in device order.

I want to find which device was received the earliest.

Please can someone advise the formula for this? Column A for device and L for date received.

Thank you :)
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,996
If the dates are in the left column you can use a V-lookup. something like :
Code:
=VLOOKUP(MIN(B2:B10),B2:C10,2,FALSE)
For the above I had dates in B2:B10 and serial Nos in C2:10. Here, the vlookup looks up the smallest date and returns the next column
 

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Hi, Thanks for this.

I have device in column A and I have time in stock in column M - is there a way to manipulate this to get the highest value for time in stock to render the device?

Thanks - not very good at VLOOKUP.
 

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Just to update - had a quick read up on basics of VLOOKUP and I saw that the lookup has to be in the left most column. Jiggled my data around and sorted it :)
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,996
Ahh good news. I was just searching how to do it formulaic (not my strong point) using offset and index/match.

Got tied up in knots and couldn't quite get it. Happy you've fixed it!
 

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Is there a simple way to modify it so that data from columns 1,2,3 and 10 are pulled into a string? So the one that has been here longest shows device, colour, memory and network all in one cell? Thanks
 

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Managed to do it - I feel this could be neater but as they say, if it's not broke, don't fix it:

Code:
=CONCATENATE(VLOOKUP(MAX(Shipped!N:N),Shipped!N:N:Shipped!P:P,3,FALSE)," ",(VLOOKUP(MAX(Shipped!N:N),Shipped!N:N:Shipped!Q:Q,4,FALSE))," ",(VLOOKUP(MAX(Shipped!N:N),Shipped!N:N:Shipped!R:R,5,FALSE))," ",(VLOOKUP(MAX(Shipped!N:N),Shipped!N:N:Shipped!Y:Y,12,FALSE)))
 

Watch MrExcel Video

Forum statistics

Threads
1,102,474
Messages
5,487,096
Members
407,577
Latest member
afonsomira

This Week's Hot Topics

Top