vlookup in stockhistory table

%3CLINGO-SUB%20id%3D%22lingo-sub-2430390%22%20slang%3D%22en-US%22%3Evlookup%20in%20stockhistory%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2430390%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%20all%2C%3C%2FP%3E%3CP%3Ei%20want%20to%20create%20a%20sheet%20that%20follows%20some%20stocks%20and%20tells%20me%20every%20stock%20when%20it%20was%20at%20the%20highes%20price%20since%20it%20was%20added%20to%20the%20sheet.%20for%20that%20i'm%20using%20vlookup%20that%20works%20on%20a%20table%20from%20%22stockhistory%22%20function.%20somehow%20instead%20of%20getting%20the%20real%20date%20of%20the%20max%20price%2C%20i%20get%20the%20latest%20date.%20i%20used%20%22evaluate%20formula%22%20to%20try%20debugging%20what's%20wrong.%20the%20vlookup%20reached%20the%20table%20correctly%20but%20didnt%20fit%20between%20the%20max%20value%20to%20its%20date%20and%20i%20dont%20know%20why.%20i'm%20adding%20the%20debugging%20window.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22197282036_837814776816217_3414105826048912022_n.png%22%20style%3D%22width%3A%20467px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F287454iED2CB010546DAAE0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22197282036_837814776816217_3414105826048912022_n.png%22%20alt%3D%22197282036_837814776816217_3414105826048912022_n.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22197450678_905382646708468_804537938393458325_n.png%22%20style%3D%22width%3A%20469px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F287453iA9713A368794DD07%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22197450678_905382646708468_804537938393458325_n.png%22%20alt%3D%22197450678_905382646708468_804537938393458325_n.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2430390%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2430436%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20in%20stockhistory%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2430436%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1074635%22%20target%3D%22_blank%22%3E%40fidtal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(E2%23%2CXMATCH(1e10%2CINDEX(E2%23%2C%2C2)%2C-1)%2C%7B1%2C2%7D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eon%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20394px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F287457i057F93B712C9787B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

hi all,

i want to create a sheet that follows some stocks and tells me every stock when it was at the highes price since it was added to the sheet. for that i'm using vlookup that works on a table from "stockhistory" function. somehow instead of getting the real date of the max price, i get the latest date. i used "evaluate formula" to try debugging what's wrong. the vlookup reached the table correctly but didnt fit between the max value to its date and i dont know why. i'm adding the debugging window.197282036_837814776816217_3414105826048912022_n.png197450678_905382646708468_804537938393458325_n.png

7 Replies

@fidtal 

As variant

=INDEX(E2#,XMATCH(1e10,INDEX(E2#,,2),-1),{1,2})

on

image.png

will it work without physically showing the table?
it returns the last row again...

@fidtal 

Yes

image.png

=LET(stock,STOCKHISTORY("msft",TODAY()-90,TODAY(),0,2,0,3),INDEX(stock,XMATCH(10000000000,INDEX(stock,,2),-1),{1,2}))

@fidtal 

Could you please share the file with the sample?

@Sergei Baklan 

the solution of index and xmatch works. didnt understand exactly how... andi dont know why vlookup didnt work

@fidtal 

With XMATCH() we find position of max looking for any big number and returning position of it or next smaller number which will be our max. INDEX() returns data on this position.

 

Why doesn't VLOOKUP() work - hard to say without the sample.