Match Index Formula Required

I did this in Excel however since both the INDEX and MATCH functions are listed in google I assume it will work.
1. Populate DL3:DP3 (Inserted) with 6 months ago, 90 days ago, 35 days ago, 15 days ago and 10 days ago
2. Populate DL5:DP8 using:

Formula:

=INDEX('Technical Analysis'!$I$2:$AE$5,MATCH($CD5,'Technical Analysis'!$H$2:$H$5,0),MATCH($DS$3,'Technical Analysis'!$I$1:$AE$1,0))/INDEX('Technical Analysis'!$I$2:$AE$5,MATCH($CD5,'Technical Analysis'!$H$2:$H$5,0),MATCH(DL$3,'Technical Analysis'!$I$1:$AE$1,0))-1

Let us know if you have any questions.

Hi, thank you, but you got it wrong https://prnt.sc/h2njqxURj8BW
You need to make it for other three stock also but it has to be the same formula.

I am not sure why you were doing it in Microsoft Excel meanwhile, you can just simply edit it right away in my Google sheet, thank you

The final formula should be just simply dragedable/copiedable (for one colomn) accross different stocks name in DL4:DP

The above statement indicates that the formula should be in columns DL:DP.
As for the formula for the cells in the "code" rows, columns DS:DT they are based on:

Formula:

=IF(COLUMN()=123,INDEX('Technical Analysis'!$I$2:$AE$5,MATCH(DR$3,'Technical Analysis'!$H$2:$H$5,0),MATCH(DS$3,'Technical Analysis'!$I$1:$AE$1,0)),INDEX('Technical Analysis'!$I$2:$AE$5,MATCH($DR$3,'Technical Analysis'!$H$2:$H$5,0),MATCH($DV4,'Technical Analysis'!$I$1:$AE$1,0)))

Adjustments have been made as the formula is transferred from the KEQU section to the SONY section etc.
Let us know if you have any questions.

Hi, thank you very much, because it seems you almost got it correctly, but you cannot make column DS become a helper column...
Because the helper column is only on column EJ:EK and also you can still close onto column CD:CE.

I am sorry to forgot to say that, column DR:DV it's just a simulation testing column.

Based on post #1 I put a formula in cells DL5:DP8 and was told that was wrong.
Based on post #3 I put a formula in the cells that were marked "Fill in here" in columns DS:DT and am now being told that is wrong also.
Please specify where the formula should go.

On your first post, basically you can see the correct result in my Google sheet in DL4:DP7 https://prnt.sc/Hp3QJr6WKbUU but as you can see your output, result is different https://prnt.sc/lpZmFiXeu2r8 . So anyhow, it means the formula is wrong

On your second post, your formula were crawling into a non-helper column. Please do not crawl into any column in range DR:DV, because that is just for an illustration for you to understand how exactly the data is organized.

The formula should be filled in DL5:DP7 while it may or may not have to crawl on the technical analysis sub sheet tab and table EJ:EK.

Thank you

In cells DL4:DP4 the percentage is the ratio of the technical analysis value from 5 days ago to the values from the times listed in DL3:DP3, which by the way there is no 3 month or 1 month on the technical analysis sheet nor in columns EJ:EK of the instruction sheet. That is why I added a new row 3 with days that match those listed on the technical analysis sheet.
Cells DL5:DP5 seem to be the ratio of technical analysis value from 10 days ago, instead of 5 days ago, to the values from the times listed in DL3:DP3. Please tell us how, without "crawl[ing] into any column in range DR:DV", we should know that.

In cells DL4:DP4 the percentage is the ratio of the technical analysis value from '5 days ago' to the values from the times listed in DL3:DP3,
=> you need to check column CE, each stock name has different date, not all of them should be compared to '5days ago'

which by the way there is no 3 month or 1 month on the technical analysis sheet nor in columns EJ:EK of the instruction sheet.
=> you can use like 90 days ago for three months, and 25/30 for one month something like that. Basically the five days is calculated for each weekdays.

That is why I added a new row 3 with days that match those listed on the technical analysis sheet.
=> unfortunately, your new role cannot be applied to our origin Google sheet. That is why there should not be modifying on EJ:EK at all, because the table is just an illustration which does not really exist in my original Google sheet.

Cells DL5:DP5 seem to be the ratio of technical analysis value from 10 days ago, instead of 5 days ago, to the values from the times listed in DL3:DP3.
=> yes
Please tell us how, without "crawl[ing] into any column in range DR:DV", we should know that.
=> I do not know what you are trying to imply, but like I said, you cannot crawls into range DR:DV, because in our original Google sheet there are so much more stock names like thousands of them, not just four of them, we cannot manually put those into the table in DR:DV.

But you can make a helper column on something like table EJ:EK, because I do not have to make so much amount of table like I would do for DR:DV


I hope you understand, thank you

This formula does not reference columns DR:DV

Formula:

=INDEX('Technical Analysis'!$I$2:$AE$5,MATCH($CD5,'Technical Analysis'!$H$2:$H$5,0),MATCH(INDEX($EK$4:$EK$24,MATCH(TODAY()-$CE5,$EJ$4:$EJ$24)),'Technical Analysis'!$I$1:$AE$1,0))/INDEX('Technical Analysis'!$I$2:$AE$5,MATCH($CD5,'Technical Analysis'!$H$2:$H$5,0),MATCH(DL$3,'Technical Analysis'!$I$1:$AE$1,0))-1

If you select the highlighted area of the formula, Lookup_value of column MATCH, each row calculates for the difference between today and the date in column CE.
Since the formula references today's date the differences between today and the dates in column CE has changed from when the values of days ago were calculated in the file attached to post #1.
The formula makes use of values in DL3:DP3 which could be moved to another row and/or hidden for aesthetic purposes.
Let us know if you have any questions.

Hi, honestly, it is very hard to comprehend what you have just explained.

But the way I see it in your last excel I think you got my point from post 2 replied that I have explained to you, regarding the do not use the non-helper column, however, as you can see here in my screen, the output result should be the same according to my instruction.
https://prnt.sc/Y2tkUdTBw8OM

In the screenshot the percentages in column DU were based on the date being July 3rd (when post #1 was made).
The percentages in columns DL:DP were based on the date being July 9th (when post #11 was made).
In the attached file I have put the July 3rd date in cell CE1 and changed the reference in the formula from TODAY() to $CE$1 and the percentages for KEQU and SONY match with the exception that there is no 20 day column in DL:DP.
Note that for KEQU 2 weeks is defined as 15 days and for SONY 2 weeks is defined as 20 days, Excel is not going to handle that discrepancy.
The problem with the HIVE percentages is that it is 5 days (not 1 as shown is cell DS31) from June 28th to July 3rd.
Similar problem with NTAP percentages.
Let us know if you have any questions.

In the screenshot the percentages in column DU were based on the date being July 3rd (when post #1 was made).
The percentages in columns DL:DP were based on the date being July 9th (when post #11 was made).
=> Basically, I have a project based on Google app script code, where it is processing the data by updating it on the frequently daily basis, so as the day changes the data will change. so your formula would require to automate those process by making a single formula, but the process is continuous.

In the attached file I have put the July 3rd date in cell CE1 and changed the reference in the formula from TODAY() to $CE$1 and the percentages for KEQU and SONY match with the exception that there is no 20 day column in DL:DP.
=> this is why it would not work, because you are creating a helper column in the source data column, besides that, it does not help the automation process because it seems you are asking me to create that manual cell for almost every time or for every stock name.

Note that for KEQU 2 weeks is defined as 15 days and for SONY 2 weeks is defined as 20 days, Excel is not going to handle that discrepancy.
=> I do not understand, because I thought that it is still possible to do this in Google sheet formula. I am not sure Also if the formula would be the same for between Excel and Google sheet.

The problem with the HIVE percentages is that it is 5 days (not 1 as shown is cell DS31) from June 28th to July 3rd.
Similar problem with NTAP percentages.
=> I am pretty confused. Also, why did you make up July 3rd for?,
you should not make it based on assumption, but based on the actual data that we got. I am seeing that you are trying to change the expected output result of which should been based on our expectation to fit into your formula, that will not help us at all.

Thank you

Administrative Note re. Forum Guideline #2:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter and rarely necessary.

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @AliGW).

If you do need to quote, limit the quoted section just to the section to which you wish to draw your helper's attention or a direct question to which you wish to respond.

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

In the attached file I have put the July 3rd date in cell CE1 and changed the reference in the formula from TODAY() to $CE$1 and the percentages for KEQU and SONY match with the exception that there is no 20 day column in DL:DP.
=> this is why it would not work, because you are creating a helper column in the source data column, besides that, it does not help the automation process because it seems you are asking me to create that manual cell for almost every time or for every stock name.

I was using cell CE1 to illustrate what the formula in post #11 would have yielded on July 3rd which is the date of the original post.
I will ask if any of the other contributors would be willing to attempt to help.
I hope that you have a blessed day.

Originally Posted by JeteMc

I was using cell CE1 to illustrate what the formula in post #11 would have yielded on July 3rd which is the date of the original post.

Thank you very much for your contribution, but it will be nice if you have had not used another cell (to become a helper column) of which is part of the automation process like CE5:CE.
However, you can still make a helper column, but in a different column other than the source data (like CD:DP).

I have been asking for so many other people also for this formula, but it seems quite challenging enough, which may even need a Google app script code instead, of that I am not sure.

Thank you

@JeteMc - you have been shown very little gratitude by the OP for trying to assist with their problem, so thank you for your efforts on their behalf. I suspect that the tone of many of their responses to you will have put others off - thanks for sticking with them for so long.

@AliGW Thank You for the encouragement. It is hard to keep helping when my efforts seemingly go unappreciated. I am thankful for times when others have helped me when I was less appreciative than I should have been, so I try to pay them back when I run into a situation like this. That said, I have gone as far as I feel obligated in this thread.

Of course you can say that something isn't working, but you need to do so in a way that does not imply that everything is your helper's fault. You have not been very encouraging with your helper on this occasion.

Please be aware that reputation is not exlusively for someone who gets the 'right' answer - you should also use it for anyone who has tried their best to help you. In this case, it would be extremely unkind of you not to award your helper some rep.