using dates with index matches

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
769
2018-081 Aug 1831 Aug 18
2018-091 Sep 1830 Sep 18
2018-101 Oct 1831 Oct 18

<tbody>
</tbody>


Please see table above.

If I have a date like 9/15/18, I want it to run a search against this table, then return the corresponding result in col A, ie 2018-09. At the moment it's a looong list of if statements, which I'll fix. I would've expected an index match to fix it but when I do that, for some reason I get 2018-08 as the answer, instead of 2018-09. I've fixed that by entering
INDEX(A1:A3,MATCH(lookupfigure,C1:C3)+1)
But why do I have to type in the +1 ?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Hi, If I understood correctly, why not just use TEXT function:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">9/15/2018</td><td style=";">2018-09</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M2</th><td style="text-align:left">=TEXT(<font color="Blue">L2,"yyyy-mm"</font>)</td></tr></tbody></table></td></tr></table><br />
 

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
769
hahah didn't know about that one! thanks!

for future reference do you think you can explain the logic to me of the original question, just so I have a better understanding?
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Glad could help and thanks for the feedback!

for future reference do you think you can explain the logic to me of the original question, just so I have a better understanding?
MATCH is being used to check for the less than argument for [match_type] (default is 1) hence it is taking it back 1 step. If you would have used INDEX(A1:A3,MATCH(lookupfigure,B1:B3) it would have worked.
 

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
769
Unless I'm misunderstanding you that's the formula I used. It was returning 1 month / 1 row prior to what I wanted which is why I added the +1 outside of the argument.
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
In MATCH, you used C1:C3 which was end of month, I am using B1:B3 which is beginning of month. End of month will move the match back 1 row when match is less than 2nd row in this case hence 1 where as beginning of month will match less than row 3 and move to row 2 so you will not need to add +1 for this.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,061
Messages
5,472,229
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top