Index and Match between Two Dates

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
365, 2019
Platform
Windows
Hey EXCEL experts, I need a formula for an index and match that looks for a number that is below another cell number. Attached is an image with more explanation. The key is to keep the index / match between the two dates on the sheet in each column.

Thanks in advance!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,904
Office Version
2016
Platform
Windows
Hi Matthew,

Firstly your image seems to have disappeared, but I think I understood the question. If I'm right you're trying to find the minimum Closing figure that occurs between two dates. IN which case this formula should help:

=MIN((A1:A81>=F6)*(A1:A81<=F7)*(B1:B81)+NOT((A1:A81>=F6)*(A1:A81<=F7))*MAX((B1:B81)+1))

you'll need to adjust the ranges to suit your data so that:
A1:A81 are the date cells,
F6 is your earliest date,
F7 is you latest date, and
B1:B81 is you closing figure.

To find the date on which the closing figure occurs do a simply match to the figure and index to the left one column.

HTH
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,904
Office Version
2016
Platform
Windows
PS. I should have said you need to enter that formula with CTRL-SHFT-ENTER as it is an array formula. (obviously time for bed!)
 

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
365, 2019
Platform
Windows
Thanks for your quick response! Hmmmmm . . . I did that with the CTRL-SHFT-ENTER and this is what I got . . . hope the image shows. The number is incorrect, it should be 3,002.10.

3,387.15 isn't even in Column F anywhere (?) Wonder how it returned it?
 

Attachments

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,904
Office Version
2016
Platform
Windows
First point is that for whatever reason the formula hasn’t been entered as an array formula as it is missing the {} around it. The formula needs to be editable in the formula bar and then press CTRL-SHFT-ENTER. The curly brackets should then appear.

Are you able to share the sheet?
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,904
Office Version
2016
Platform
Windows
Hi Matthew,

Now had the chance to try some more. Not sure why you getting the result you are. My table and formula lare as shown below. The key think to not is the curly brackets around the formula, which occurs when CTRL-SHFT-ENTER is used to enter the formula, your screen shot shows these are missing.

The formula to find the Date Daily Closed Under is: =OFFSET($B$15,MATCH(I$7,$F$16:$F$10000,0),0)

Not sure why you're getting 3387.15 though as without the proper array formula entry I get #VALUE error. However, the figure itself is probably the maximum +1 which is what I use to mask out all the figures outside of the date range of interest - its the bit of the formula that starts with 'NOT(...'. Interestingly, if there was no data between the dates specified then it would return the Max+1 as that would appear to be the minimum, which might be a problem, though we can sort it.

For info, I would be tempted to convert the source data to a Table, import to PowerQuery and then use PowerPivot to produce the output. But that would require quite a bit more work :).

HTH

1593859890511.png
 

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
365, 2019
Platform
Windows
Thank you for attention to this. While I was able to fix and get the correct result of 3,002.10, when I copied and pasted the array across multiple rows, it gives me the lowest value in that column that closed under the low. I did NOT explain better what I was looking for . . . I need the FIRST number the closes UNDER that low, not the lowest number under that close. I apologize. There are a lot of moving parts to my trading system and I need to know the FIRST number that closes under that low on June 2, 2020. Yes, the correct answer for that column is 3,002.10. But it's now giving me the lowest low (MIN?) in all the other columns . . . Attached is an image with the correct numbers to the far right on a couple of other examples. But your array gives the lowest, not these numbers. So I should have said, "return the first number from column F that closes below the I2 low from that date and that first low must come from the closes between I2 and I3. Sometimes, the answer is nothing because the range (60 bars) doesn't have a close below the low from date I2. That's why you see blanks on some of those in the image. If I need to send you the spreadsheet, how do I attach?
 

Attachments

Last edited:

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
365, 2019
Platform
Windows
In other words, if there isn't a low in that 60 day range that closed under I2, it should return a blank. Sorry, I moved the date for I3 down, it's now I9
 

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
365, 2019
Platform
Windows
I have the spreadsheet ready to attach -- how do you attach it? I just see mage and URL (?)
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,904
Office Version
2016
Platform
Windows
hmmm, actually I think you did explain what you wanted in the original post, I just didn't understand. I think what you want is a more difficult challenge - and is therefore interesting! I've seen spreadsheets attached using dropbox if that helps.

I'm not sure how I'll tackle this challenge, but I would think its possible!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,235
Messages
5,485,565
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top