Forum Discussion
look up and select most recent date from list
what function can I put into a cell to select the sample number with most recent date when using VLOOKUP to select a location code? I cannot take these away and run a sort in a separate spreadsheet as there are many queries to run at the one time. Looking for a solution formula to go into a cell. Attached file: location column 1, sample number column 2, and date sampled column 3. Vlookup finds the desired location in column 1. Thanks
6 Replies
This can also be achieved by MAX IF function which is accomplished by VLOOKUP.
I have enclosed the work done on data shared.
ShishirKumar I now see that my proposed solution is also flawed. Continuing to work on your suggestions.
ShishirKumar OK thanks. I was not clear enough with my question. I was seeking to select, for a selected location value (column 1), the sample number (coumn 2), for the m ost recent sampling recorded for all sites (column 3). This query is driven from a separate sheet and looking to put a single value, the sample number, into a cell on that separate sheet. I was not looking to list most recent dates alongside the existing data table.
My solution appears to be MAX(VLOOKUP($A8,'TEST DATA'!B$4:H$67,4,FALSE)) where column '4' holds the sample numbers and column 5 holds the relative date.
Cheers and thanks for succinct and prompt responses. Don
Hello DonPollockTC
I have used =VLOOKUP(MAX(A:A),A:B,2,FALSE)
to find out most recent location. See the attached data.
Hope it what you are searching for.
OK thanks. I was not clear enough with my question. I was seeking to select, for a selected location value (column 1), the sample number (coumn 2), for the m ost recent sampling recorded for all sites (column 3). This query is driven from a separate sheet and looking to put a single value, the sample number, into a cell on that separate sheet. I was not looking to list most recent dates alongside the existing data table.
My solution appears to be MAX(VLOOKUP($A8,'TEST DATA'!B$4:H$67,4,FALSE)) where column '4' holds the sample numbers and column 5 holds the relative date.
Cheers and thanks for succinct and prompt responses. Don
AshaKantaSharma Beauty is in the simplicity. Thank you.
What syntax do i use when I am driving this query from another sheet? Do I put 'lookup value' before 'MAX' and then the 'table array' and so on? My 'lookup value' is my location value, and i seek to have the smaple number selected.
