A little formula help - vlookup with a return of "latest date"

Domroy

Board Regular
Joined
Mar 8, 2018
Messages
114
I have a file with several tabs. On my "Dashboard" tab, I have a listing of assets with a unique identifier in column A. In column C, I want to put a formula that will go to the "Hour Meter Reading" tab, look at the array (where there are multiple listings of each asset number for each time the meter was checked on the part). I want it to look in the array (column A is the asset ID, and column K is the date and L is the time), and pull out the most recent date/time <K and L> for that asset, which, again, is listed multiple times, as it has multiple meter readings.

Help?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
is this something you want.

where column A id has date
=VLOOKUP(LARGE(A:A,1),A:B,2,0)
 
Upvote 0
Maybe, yes. But I'm looking for data on another tab (called Hour Meter Readings), and having trouble making this array formula work.

So I'm looking to create something like =Vlookup from column A on Dashboard, to entire table in Hour Meter Recordings, returning the most recent of column 11 for that ID number. Make sense? Your formula doesn't include moving to another sheet, and I'm having trouble with where to put the array formula.
 
Upvote 0
my apologies coudn't understand whole scenario.... would you post image of three thing..

1) lookoup value ---> sheet
2)table Array ----> probbaly "hour merter recording tab
3) and if possible output sample.
 
Upvote 0
. would you post image of three thing..
Images are of little use to anyone, samples are best posted using XL2BB (that's the green button to the right of the reply box toolbar).

Getting most recent date and time combined from 2 separate columns is not really a practical task, with additional criteria it becomes even less practical. The best thing to do would be to add a simple formula to column M of the Hour Meter Reading sheet to combine the date and time, i.e. in M2 and filled down to the end of the data range, =K2+L2

Then get the max (most recent) date and time combination from that column using something like

=MAXIFS('Hour Meter Reading'!M:M,'Hour Meter Reading'!A:A,Dashboard!A2)
 
Upvote 0
I couldn't make it work. I also can't seem to get the XL2BB to download. I'm on Excel 2016. Is there another filesharing services that I can use to share?
 
Upvote 0
With 2016 you wouldn't have the MAXIFS function, you would need to use something like this instead, adjust the end of the 2 ranges as necessary to cover your data range. This type of formula should not be used with entire columns.

=AGGREGATE(14,6,'Hour Meter Reading'!$M$2:M$M$200/('Hour Meter Reading'!$A$2:$A$200=Dashboard!A2),1)

XL2BB is preferred over file sharing, many users are not able to download workbooks from share sites.

If you click the button on the reply bar, then the orange button top right of the guide page it will download the add in as a zip file, you need to unzip that then follow the instructions to install it.
Please note that if you need to move the XL2BB add in file after downloading / extracting it, you need to do so before following the instructions to add it to excel.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top