using dates with index matches

[bigdan]

[TABLE="width: 500"]
<tbody>[TR]
[TD]2018-08[/TD]
[TD]1 Aug 18[/TD]
[TD]31 Aug 18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018-09[/TD]
[TD]1 Sep 18[/TD]
[TD]30 Sep 18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018-10[/TD]
[TD]1 Oct 18[/TD]
[TD]31 Oct 18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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 ?

 

[Aryatect]

Hi, If I understood correctly, why not just use TEXT function:

Book1
	L	M
2	9/15/2018	2018-09
Sheet4

Cell Formulas
Range	Formula
M2	=TEXT(L2,"yyyy-mm")

 

[bigdan]

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]

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]

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]

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.

 

[bigdan]

makes sense. thank you sir / maam!

 

[Aryatect]

Glad could help