Auto populate values from raw data using multiple criteria

1. 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

2. Re: Auto populate values from raw data using multiple criteria

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,),)

3. 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. 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

5. 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.``

6. 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

7. 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,"")

8. Re: Auto populate values from raw data using multiple criteria

Originally Posted by Bo_Ry
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 !

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