Objective:
- I have rows of numbers on the second sheet.
- The array to search on the second sheet are the rows with date/times (A3:A?) between the date/time in B3 and C3 on first sheet
- I want the formula to start looking in the first row that is >= B3 in first sheet, for numbers that are greater than 0.001 than the number in I3 on the first sheet. (Ex: number > (I3+0.001)
- I want to enter a variable into the cell S1 on the first sheet of how many matches must be found (Example: 1, 2, 3, 4)
- For those numbers that are greater than I3+0.001, it needs to find the number of occurrences set by the variable in cell S1 that are no more than +or- 0.0001 variance away from each other
- Then when it finds the number of occurrences set by the S1 variable, it checks that number (this cell value to check against can be any one of the occurrences because they have low variance. Example is it could be the first occurrence) against that row's number found in the column L.
- (this example assumes the S3 variable is 3) If on the first sheet, cell E3 says "Buy", then it checks when "L3>the number found that has 3 occurrences". If E3 says "Sell", then it checks if "L3 < the number found that has 3 occurrences".
- If the above is true (L3 is higher or lower), then the formula returns the date/time (A column) from the second sheet.
- If it is not true, it goes to the next row and checks that rows numbers.
- It continues until it gets the last date in sheet one found in C3 (<=)
I tried to start this using Index/Match, but then realized I was out of my depth. I attached an example workbook.
The narrative, in case it helps to understand the context:
I have past trades on the Trade Analysis sheet (first sheet). On the EURUSD sheet (second sheet), I have historical data for the EURUSD and then computed 'obstacles' (starting column N). I am wanting to see if I exited the trade when there are multiple obstacles at one price level.
I know this might be complicated, but I really appreciate the help, because I am not able to figure it out. Thank you in advance!
Bookmarks