vertical lookup

%3CLINGO-SUB%20id%3D%22lingo-sub-1620685%22%20slang%3D%22en-US%22%3Evertical%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620685%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3EI%20want%20to%20use%20vertical%20lookup%20on%20time%20values.%26nbsp%3B%3CBR%20%2F%3EI%20have%20a%20matrix%20with%20in%20the%20first%20Collum%20date%20and%20time%20and%20in%20the%20second%20Collum%20a%20value.%3CBR%20%2F%3EBut%20the%20normal%20vert.lookup%20function%20does%20not%20work.%3CBR%20%2F%3EIs%20there%20a%20solution%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3ETnx%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1620685%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1620723%22%20slang%3D%22en-US%22%3ERe%3A%20vertical%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620723%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F775471%22%20target%3D%22_blank%22%3E%40Lei_Nacken%3C%2FA%3E%26nbsp%3BWhat%20exactly%20are%20you%20trying%20to%20lookup%2C%20and%20how%3F%20For%20instance%2C%20the%20date%2Ftime%20for%20%2229%2F08%2F2020%2015%3A46%3A11%22%20is%20stored%20in%20Excel%20as%20the%20number%2044072.6570717593000%20The%20time%20portion%20alone%2C%20i.e.%20%2215%3A46%3A11%22%20equals%20the%20number%26nbsp%3B0.657071759259259.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20have%20to%20split%20the%20time%20and%20date%20column%20first%2C%20then%20round%20the%20time%2010%20digits%20in%20order%20to%20be%20able%20to%20lookup%20the%20time%20value%20only.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1621286%22%20slang%3D%22en-US%22%3ERe%3A%20vertical%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621286%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHi%3CBR%20%2F%3EThank%20you%20so%20much%20for%20your%20answer.%20You%20sent%20me%20in%20the%20right%20direction.%20I%20solved%20my%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,
I want to use vertical lookup on time values. 
I have a matrix with in the first Collum date and time and in the second Collum a value.
But the normal vert.lookup function does not work.
Is there a solution?


Tnx in advance

2 Replies

@Lei_Nacken What exactly are you trying to lookup, and how? For instance, the date/time for "29/08/2020 15:46:11" is stored in Excel as the number 44072.6570717593000 The time portion alone, i.e. "15:46:11" equals the number 0.657071759259259.

 

You may have to split the time and date column first, then round the time 10 digits in order to be able to lookup the time value only.

@Riny_van_Eekelen 
Hi
Thank you so much for your answer. You sent me in the right direction. I solved my problem.