look up and select most recent date from list

%3CLINGO-SUB%20id%3D%22lingo-sub-1388245%22%20slang%3D%22en-US%22%3Elook%20up%20and%20select%20most%20recent%20date%20from%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388245%22%20slang%3D%22en-US%22%3E%3CP%3Ewhat%20function%20can%20I%20put%20into%20a%20cell%20to%20select%20the%20sample%20number%20with%20most%20recent%20date%20when%20using%20VLOOKUP%20to%20select%20a%20location%20code%3F%26nbsp%3B%20I%20cannot%20take%20these%20away%20and%20run%20a%20sort%20in%20a%20separate%20spreadsheet%20as%20there%20are%20many%20queries%20to%20run%20at%20the%20one%20time.%26nbsp%3B%20Looking%20for%20a%20solution%20formula%20to%20go%20into%20a%20cell.%26nbsp%3B%20Attached%20file%3A%26nbsp%3B%20location%20column%201%2C%20sample%20number%20column%202%2C%20and%20date%20sampled%20column%203.%26nbsp%3B%20Vlookup%20finds%20the%20desired%20location%20in%20column%201.%26nbsp%3B%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1388245%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1388277%22%20slang%3D%22en-US%22%3ERe%3A%20look%20up%20and%20select%20most%20recent%20date%20from%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388277%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F615270%22%20target%3D%22_blank%22%3E%40DonPollockTC%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20used%26nbsp%3B%3DVLOOKUP(MAX(A%3AA)%2CA%3AB%2C2%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20find%20out%20most%20recent%20location.%20See%20the%20attached%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20what%20you%20are%20searching%20for.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1388352%22%20slang%3D%22en-US%22%3ERe%3A%20look%20up%20and%20select%20most%20recent%20date%20from%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402789%22%20target%3D%22_blank%22%3E%40AshaKantaSharma%3C%2FA%3E%26nbsp%3BBeauty%20is%20in%20the%20simplicity.%26nbsp%3B%20Thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20syntax%20do%20i%20use%20when%20I%20am%20driving%20this%20query%20from%20another%20sheet%3F%26nbsp%3B%20Do%20I%20put%20'lookup%20value'%20before%20'MAX'%20and%20then%20the%20'table%20array'%20and%20so%20on%3F%26nbsp%3B%20My%20'lookup%20value'%20is%20my%20location%20value%2C%20and%20i%20seek%20to%20have%20the%20smaple%20number%20selected.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1389153%22%20slang%3D%22en-US%22%3ERe%3A%20look%20up%20and%20select%20most%20recent%20date%20from%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1389153%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F615270%22%20target%3D%22_blank%22%3E%40DonPollockTC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20can%20also%20be%20achieved%20by%20MAX%20IF%20function%20which%20is%20accomplished%20by%20VLOOKUP.%3C%2FP%3E%3CP%3EI%20have%20enclosed%20the%20work%20done%20on%20data%20shared.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1391013%22%20slang%3D%22en-US%22%3ERe%3A%20look%20up%20and%20select%20most%20recent%20date%20from%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1391013%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F665807%22%20target%3D%22_blank%22%3E%40ShishirKumar%3C%2FA%3E%26nbsp%3BOK%20thanks.%26nbsp%3B%20I%20was%20not%20clear%20enough%20with%20my%20question.%26nbsp%3B%20I%20was%20seeking%20to%20select%2C%20for%20a%20selected%20location%20value%20(column%201)%2C%20the%20sample%20number%20(coumn%202)%2C%20for%20the%20m%20ost%20recent%20sampling%20recorded%20for%20all%20sites%20(column%203).%26nbsp%3B%20This%20query%20is%20driven%20from%20a%20separate%20sheet%20and%20looking%20to%20put%20a%20single%20value%2C%20the%20sample%20number%2C%20into%20a%20cell%20on%20that%20separate%20sheet.%26nbsp%3B%20I%20was%26nbsp%3B%20not%20looking%20to%20list%20most%20recent%20dates%20alongside%20the%20existing%20data%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20solution%20appears%20to%20be%26nbsp%3BMAX(VLOOKUP(%24A8%2C'TEST%20DATA'!B%244%3AH%2467%2C4%2CFALSE))%20where%20column%20'4'%20holds%20the%20sample%20numbers%20and%20column%205%20holds%20the%20relative%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%20and%20thanks%20for%20succinct%20and%20prompt%20responses.%26nbsp%3B%20Don%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1391020%22%20slang%3D%22en-US%22%3ERe%3A%20look%20up%20and%20select%20most%20recent%20date%20from%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1391020%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402789%22%20target%3D%22_blank%22%3E%40AshaKantaSharma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOK%20thanks.%26nbsp%3B%20I%20was%20not%20clear%20enough%20with%20my%20question.%26nbsp%3B%20I%20was%20seeking%20to%20select%2C%20for%20a%20selected%20location%20value%20(column%201)%2C%20the%20sample%20number%20(coumn%202)%2C%20for%20the%20m%20ost%20recent%20sampling%20recorded%20for%20all%20sites%20(column%203).%26nbsp%3B%20This%20query%20is%20driven%20from%20a%20separate%20sheet%20and%20looking%20to%20put%20a%20single%20value%2C%20the%20sample%20number%2C%20into%20a%20cell%20on%20that%20separate%20sheet.%26nbsp%3B%20I%20was%26nbsp%3B%20not%20looking%20to%20list%20most%20recent%20dates%20alongside%20the%20existing%20data%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20solution%20appears%20to%20be%26nbsp%3BMAX(VLOOKUP(%24A8%2C'TEST%20DATA'!B%244%3AH%2467%2C4%2CFALSE))%20where%20column%20'4'%20holds%20the%20sample%20numbers%20and%20column%205%20holds%20the%20relative%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%20and%20thanks%20for%20succinct%20and%20prompt%20responses.%26nbsp%3B%20Don%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1392235%22%20slang%3D%22en-US%22%3ERe%3A%20look%20up%20and%20select%20most%20recent%20date%20from%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1392235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F665807%22%20target%3D%22_blank%22%3E%40ShishirKumar%3C%2FA%3E%26nbsp%3B%20I%20now%20see%20that%20my%20proposed%20solution%20is%20also%20flawed.%20%26nbsp%3BContinuing%20to%20work%20on%20your%20suggestions.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

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.

Highlighted

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.

Highlighted

@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

Highlighted

@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

Highlighted

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