last value of the month

[h5lui]

Hello, I'm new to the forum
I'm trying to figure out how to get the last value of each month from the data below,
How do I get the result in column D?

Thanks so much.

excel.JPG

[Richard Buttrey]

Please upload an example workbook. Pictures are rarely much use.

[Moo the Dog]

See my attached sheet...

It uses the following ARRAY formula to return the value you're looking for:

Please Login or Register  to view this content.

Remember, ARRAY formulas are applied by pressing CTRL+SHIFT+ENTER, rather than just ENTER.

You'll notice that the list in column C actually contains dates, not text. They are formatted to show "Jan 2017, Dec 2017...) but they are actually (1/1/2017, 12/1/2016...)

Hope this helps.

- Moo

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1	Date	Value	------	Month	Value
2	1/8/2017	1.51		Jan	1.51
3	1/2/2017	1.4		Dec	1.46
4	12/19/2016			Nov	1.32
5	12/12/2016	1.46		Oct	1.48
6	12/5/2016	1.3
7	11/28/2016
8	11/21/2016
9	11/14/2016	1.32
10	11/7/2016	1.24
11	10/31/2016
12	10/24/2016
13	10/17/2016	1.48
14	10/10/2016	1.32
15	10/3/2016	1.39

This array formula** entered in E2 and copied down:

=INDEX(B$2:B$15,MATCH(MAX(IF(TEXT(A$2:A$15,"mmm")=D2,IF(B$2:B$15<>"",A$2:A$15))),A$2:A$15,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[AlKey]

Maybe this
Enter array formula in D2 and copy down

Please Login or Register  to view this content.

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

v	A	B	C	D
1	Date	Value		Last Value
2	1/9/2017	1.51	Jan	1.51
3	1/2/2017	1.4	Dec	1.46
4	12/19/2016		Nov	1.32
5	12/12/2016	1.46	Oct	1.48
6	12/5/2016	1.3
7	11/28/2016
8	11/21/2016
9	11/14/2016	1.32
10	11/7/2016	1.24
11	10/31/2016
12	10/24/2016
13	10/17/2016	1.48
14	10/10/2016	1.32
15	10/3/2016

[h5lui]

Thank you so much to all of you, I don't know much about array, and I hope to learn more!
Sorry I didn't know how to post a table before, just figured out I needed to "go advanced" for that.

[Tony Valko]

You're welcome. We appreciate the feedback!