Home >> Support Board

Support Board

Date/Time: Sun, 28 Apr 2024 07:29:29 +0000

How to Post a Message for Support

[Programming Help] - I'm not able to make GETCORRESPONDINGMATCH spreadsheet function to works

View Count: 1300

[2018-11-05 18:53:30] #1 [Go To First Post]
User345323 - Posts: 18
Dear SC, I'm trying to use =GETCORRESPONDINGMATCH() function to works with following example. My data are as in the attached image. My function is: =GETCORRESPONDINGMATCH(AB3:AB9,AC1,0,1,0,AA3:AA9,0) AB3:AB9 >>>>> SearchColumn = 4,6,4,2,4,2,4 AC1 >>>>> SearchValue = 2 0 >>>>> NearestOrExact = Exact 1 >>>>> SearchRangeOrderingFlag = Ascending 0 >>>>> SameValueResolution = Use lower numbered row index, but with 1 nothing change AA3:AA9 >>>>> ReturnResultColumn = 3,4,5,6,7,8,9 0 >>>>> ReturnResultRowOffset = No row offset For me result =2 is not correct. And why if AC1=4 I get #N/A ? What I'm doing wrong? Very simply, I want to search range AB3:AB9 for number 2 for example, and looking from bottom to top of range, get as result the correspondenting row, in this case row n° 8. Any help with alternative functions? Thank you


example.jpg / V - Attached On 2018-11-05 18:25:25 UTC - Size: 26.88 KB - 301 views

[2018-11-05 20:50:02] #2 [Go To First Post]
User345323 - Posts: 18
I'm sorry, I get my error. I undrestood in a wrong way "SearchRangeOrderingFlag" field. That's not how to search, from top to bottom of range or viceversa, but how data are ordered, so I've to insert "0" value because my search column is unordered. So if I use a "0" in this field I've same result as a combination of INDEX and MATCH functions, looking for result from TOP to BOTTOM of range. But, how to search column from BOTTOM to TOP of range? Any help please? Date Time Of Last Edit: 2018-11-05 20:50:39

[2018-11-06 03:52:44] #3 [Go To First Post]
Sawtooth - Posts: 3980
Both the GETCORRESPONDINGMATCH function, and the INDEX/MATCH function combination, return the value in the cell, not the row # where that value is located. If you want to return the row # of the furthest matching value, use the MATCH function in a formula like this: =MATCH($AC$1,AB3:AB9,1)+2 If you want to return the row # of the nearest matching value, use the MATCH function in a formula like this: =MATCH($AC$1,AB3:AB9,0)+2 The +2 is an offset to compensate for the first row being row 3.

[2018-11-06 19:25:43] #4 [Go To First Post]
User345323 - Posts: 18
Hello tomgilb, thank you for your reply. Your first function doesen't work correctly for my needs, because if I search AC1=6, result is row 9 and not row 4. Second function works correctly but in the same way as GETCORRESPONDINGMATCH, looking from ROW 3 to ROW 9. What if I need to look from ROW 9 to ROW 3? Thank you

[2018-11-06 23:36:39] #5 [Go To First Post]
Sawtooth - Posts: 3980
I don't know why =MATCH($AC$1,AB3:AB9,1)+2 doesn't see the 6. =NMATCH(1,$AC$1,AB3:AB9,0)+2 does see it. Have a look at the NMATCH function. You'll need a separate Formula Column for each use of the NMATCH. https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#NMATCH_Function FYI, this formula returns the same as =MATCH($AC$1,AB3:AB9,0)+2 where the formula is in Q3: =IF(AB3=$AC$1,3,Q4+1) The 3 is the starting value to be incremented to compensate for the first row being row 3 (1+2=3). Also, look at this example of a way to sort the array in the opposite direction: https://www.sawtoothtrade.com/example-8.html You can modify this example to sort a portion of the array. Once the array is sorted in the opposite direction, you can use =MATCH($AC$1,AB3:AB9,0)+2 or =IF(AB3=$AC$1,3,Q4+1) which does see the 6.

To post a message in this thread, you need to log in with your Sierra Chart account: