Home

Find next price in data set

%3CLINGO-SUB%20id%3D%22lingo-sub-780809%22%20slang%3D%22en-US%22%3EFind%20next%20price%20in%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780809%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20series%20of%20data%20which%20involves%20a%20range%20of%20products.%20I%20am%20aiming%20to%20find%20the%20price%20for%20each%20product%20in%20the%20set%20of%20data.%20I%20have%20attached%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-780809%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-780883%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20next%20price%20in%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780883%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20Stock%20name%20selection%20in%20G2%20and%20wish%20to%20list%20dates%20and%20prices%20for%20the%20selected%20product%20starting%20from%20range%20G3%20for%20date%20and%20H3%20for%20price%2C%20you%20can%20use%20the%20following%20formulas%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EG3%3A%20%26nbsp%3B%3CFONT%3E%3DIFERROR(INDEX(%24B%243%3A%24B%247%2CSMALL(IF(%24A%243%3A%24A%247%3D%24G%242%2CROW(%24B%243%3A%24B%247)-ROW(%24A%242))%2CROW(A3)-ROW(%24A%242)))%2C%22%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3EH3%3A%26nbsp%3B%3CFONT%3E%3DIFERROR(INDEX(%24C%243%3A%24C%247%2CSMALL(IF(%24A%243%3A%24A%247%3D%24G%242%2CROW(%24C%243%3A%24C%247)-ROW(%24A%242))%2CROW(A3)-ROW(%24A%242)))%2C%22%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20formulas%20must%20be%20array%20entered%20in%20each%20cell%20.%20If%20you%20then%20copy%20these%20formulas%20down%2C%20the%20next%20rows%20will%20show%20next%20date%2Fprice%20combination%20for%20the%20selected%20product.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20example%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EYury%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781041%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20next%20price%20in%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781038%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20next%20price%20in%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGiven%20the%20Stock%20and%20Date%20in%20F3%20and%20F4%2C%20respectively%2C%20the%20formula%20in%20F5%20to%20return%20the%20next%20price%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOOKUP(2%2C1%2F((A3%3AA7%3DF3)*(B3%3AB7%26gt%3BF4)*B3%3AB7%3DAGGREGATE(15%2C6%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E1%2F((A3%3AA7%3DF3)*(B3%3AB7%26gt%3BF4))*B3%3AB7%2C1))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EC3%3AC7)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EFor%20an%20unknown%20reason%2C%20I%20cannot%20attach%20the%20file%20despite%20several%20attempts%20to%20do%20so.%20There%20must%20have%20been%20a%20bug%20when%20the%20manner%2C%20by%20which%20files%20shall%20henceforth%20be%20attached%2C%20was%20modified.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782748%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20next%20price%20in%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782748%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F15833%22%20target%3D%22_blank%22%3E%40Yury%20Tokarev%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%2C%20this%20matches%20my%20needs%20perfectly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emuch%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782750%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20next%20price%20in%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782750%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20able%20to%20use%20the%20formula%20without%20the%20example%2C%20thank%20you%20for%20your%20time%20and%20assistance%2C%20it%20also%20solved%20my%20needs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E
calof1
Contributor

Hi,

 

I have a series of data which involves a range of products. I am aiming to find the price for each product in the set of data. I have attached an example.

 

Can someone please assist.

 

Kind regards,

4 Replies

Hi @calof1,

 

If you have Stock name selection in G2 and wish to list dates and prices for the selected product starting from range G3 for date and H3 for price, you can use the following formulas:

 

G3:  =IFERROR(INDEX($B$3:$B$7,SMALL(IF($A$3:$A$7=$G$2,ROW($B$3:$B$7)-ROW($A$2)),ROW(A3)-ROW($A$2))),"")

H3: =IFERROR(INDEX($C$3:$C$7,SMALL(IF($A$3:$A$7=$G$2,ROW($C$3:$C$7)-ROW($A$2)),ROW(A3)-ROW($A$2))),"")

 

Both formulas must be array entered in each cell . If you then copy these formulas down, the next rows will show next date/price combination for the selected product.

 

Please see the example attached

 

Thanks

Yury

 

@calof1 

Given the Stock and Date in F3 and F4, respectively, the formula in F5 to return the next price is: 

=LOOKUP(2,1/((A3:A7=F3)*(B3:B7>F4)*B3:B7=AGGREGATE(15,6,
1/((A3:A7=F3)*(B3:B7>F4))*B3:B7,1)),
C3:C7)

For an unknown reason, I cannot attach the file despite several attempts to do so. There must have been a bug when the manner, by which files shall henceforth be attached, was modified. 

Hi@Yury Tokarev 

 

Thanks for your help, this matches my needs perfectly.

 

much appreciated.

 

Kind regards,

Hi@Twifoo 

 

Thanks for the message.

 

I was able to use the formula without the example, thank you for your time and assistance, it also solved my needs.

 

Much appreciated.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies