look up and select most recent date from list

Copper Contributor

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

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.

@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.

Hi@DonPollockTC 

This can also be achieved by MAX IF function which is accomplished by VLOOKUP.

I have enclosed the work done on data shared.

@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

@AshaKantaSharma 

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

@ShishirKumar  I now see that my proposed solution is also flawed.  Continuing to work on your suggestions.