+ Reply to Thread
Results 1 to 8 of 8

Auto populate values from raw data using multiple criteria

  1. #1
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    204

    Auto populate values from raw data using multiple criteria

    Hi!

    I need help in writing a formula to fetch the correct results. I have tried vlookup, match, index and few other syntax but unable to achieve what I want to. Appreciate some help from you guys. My problem is described below. Thanks for looking into it.

    The region highlighted in yellow is the place where I need formulas that will auto-populate data from the raw data given in row 5:7

    Let me explain:
    F2 = In this cell, I need the close of date mentioned in cell A2 for the symbol "Temp" (B2)
    Subsequent columns from G2:K2 = Here I need the high value of next 5 available dates in the raw data from the date mentioned in cell A2 for the symbol "Temp" (B2). Keep cell blank if enough data is not available. Eg. if the date in A2 is 18-Feb, then only 4 future dates are available in the raw data, in such a case, K2 will be blank.
    L2 = In this cell I need to put after how many days, the value reached greater than or equal to 5% compared to value in cell F2. In this example, on the third day the value jumped to 9% and hence the number 3. Show zero if the value does not cross 5%

    I am attaching the worksheet. Kindly assist me on this.
    sheetscreenshot.png
    Thank you
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,772

    Re: Auto populate values from raw data using multiple criteria

    Please try at
    F2
    =INDEX(B7:M7,MATCH(A2,B5:M5,))

    G2:K2
    =MOD(SMALL(IF(($B5:$M5>$A2)*($B$6:$M$6="High"),$B$5:$M$5*10^6+$B$7:$M$7),COLUMNS($G2:G2)),10^6)
    Ctrl+Shift+Enter

    L2
    =MATCH(TRUE,INDEX(G2:K2/F2>=1.05,),)
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    204

    Re: Auto populate values from raw data using multiple criteria

    I just happened to refresh the page and saw a solution.. I was quite impressed to see a quick solution. Thank you and I really appreciate. However, I did not see the symbol being referred anywhere in the formula.

    The raw data is posted is just a sample and it will have more records. The formula should search for the symbol in the raw data and then populate values. The symbol is also a criteria to be checked. I hope I have explained it properly.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,772

    Re: Auto populate values from raw data using multiple criteria

    F2
    =INDEX(B7:M9,MATCH(B2,A7:A9,),MATCH(A2,B5:M5,))

    G2:K2
    =MOD(SMALL(IF(($B5:$M5>$A2)*($B$6:$M$6="High"),$B$5:$M$5*10^6+INDEX($B$7:$M$9,MATCH($B2,$A$7:$A$9,),)),COLUMNS($G2:G2)),10^6)
    Ctrl+Shift+Enter

    L2
    =MATCH(TRUE,INDEX(G2:K2/F2>=1.05,),)
    Attached Files Attached Files
    Attached Files Attached Files

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Auto populate values from raw data using multiple criteria

    Another Non Array formula solution:

    F2:
    Please Login or Register  to view this content.
    G2:
    Please Login or Register  to view this content.
    Copy to H:K

    L2:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    204

    Re: Auto populate values from raw data using multiple criteria

    Thank you @Bo_Ry & @bebo021999 for your kind assistance.

    I understood the top and bottom formula in F2 and L2 for both the solutions provided but not the middle one. I used formula auditing to understand the second formula (G2:K2) especially the array one and still struggling with it. Will keep on trying.

    Meanwhile, I drew another table and tried to applied the logic but not getting proper results. I request you to help me tweak the formula you guys gave in the previous post or a new formula with a new logic to get the desired results in this new table.

    sheetsscreenshot1.png

    1. Lookup date and symbol in the raw data and then populate OHLC in the table above for five days in cells highlighted in yellow
    2. in column Z, analyze all H headings from H1 to H4 to find out which H is above 5% compared to the value available in column E (heading-Manual). The first H in column G to be ignored.. just check H1 to H4 in column K O S & W
    3. and in the next column enter the percentage value how much it was above 5%

    I am attaching the revised excel sheet
    Attached Files Attached Files

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,772

    Re: Auto populate values from raw data using multiple criteria

    Try

    F2:Y
    =IFERROR(INDEX($B$8:$Y$10,MATCH($B2,$A$8:$A$10,),MATCH($A2,$B$6:$Y$6,)+COLUMNS($F2:F2)-1),"")

    Z2
    =IFERROR(ROUNDUP(MATCH(TRUE,INDEX(N(+K2:Y2)>=1.05*$E2,),)/4,),"")

    AA2
    =IFERROR(INDEX(K2:Y2,MATCH(TRUE,INDEX(N(+K2:Y2)>=1.05*$E2,),))/E2-1,"")
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    204

    Re: Auto populate values from raw data using multiple criteria

    Quote Originally Posted by Bo_Ry View Post
    Try

    F2:Y
    =IFERROR(INDEX($B$8:$Y$10,MATCH($B2,$A$8:$A$10,),MATCH($A2,$B$6:$Y$6,)+COLUMNS($F2:F2)-1),"")

    Z2
    =IFERROR(ROUNDUP(MATCH(TRUE,INDEX(N(+K2:Y2)>=1.05*$E2,),)/4,),"")

    AA2
    =IFERROR(INDEX(K2:Y2,MATCH(TRUE,INDEX(N(+K2:Y2)>=1.05*$E2,),))/E2-1,"")
    Thank you so much for your kind help. God bless !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Auto-populate cell based on multiple criteria with date range
    By rockriscia in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2020, 04:53 AM
  2. [SOLVED] Giving items multiple values which auto-populate in certain cells
    By rollsy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2018, 10:18 AM
  3. Replies: 2
    Last Post: 04-13-2015, 08:29 AM
  4. [SOLVED] Formula to auto populate cells to match multiple tabs with certain criteria
    By rowena229 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 03-18-2015, 04:42 PM
  5. How to auto populate specific data to a different worksheet based off a criteria
    By excelnewbie716 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-31-2014, 03:27 PM
  6. Auto-Populate Data From Master List to Separate Sheets When Two Criteria's Are Met
    By centibttrfly in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-09-2014, 11:18 AM
  7. Replies: 9
    Last Post: 12-13-2010, 01:42 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1