# Select last value from a column with conditions

1. ## Select last value from a column with conditions

Dear all,

First of all, thank you for your help.

I'm dealing with a problem in excel and I don't know how to solve it.

This is my column:

A1: 2/1/2015 B1: 9.12
A2: 2/10/2015 B2: 9.31
A3: 3/2/2015 B3: 0.98
A4: 5/11/2015 B4: 21.1
A5: 8/1/2015 B5: 54.2
A6: 10/29/2015 B6: 43.2

What I need to do is a formula that selects the last value from B column take into account the last value of certain date (A Column).

Example: I need to know the last value of end of february. The value must be 9.31. If I search for the last value below 31/3/2015 the output value should be 0.98.

Can you help me how to do it?

2. ## Re: Select last value from a column with conditions

Assuming it is sorted by date then try this:
Formula:

This is an array formula and needs to be entered with Ctrl + Shift + Enter

3. ## Re: Select last value from a column with conditions

Is column A in increasing order? If yes, assuming C1 contains month (it could be Jan, january or 1, or feb, february or 2,...)

=LOOKUP(EOMONTH(DATEVALUE(1&"/"&C1&"/2015"),0),\$A\$1:\$A\$6,\$B\$1:\$B\$6)

4. ## Re: Select last value from a column with conditions

Hi fneves and welcome to the forum,

I like pivot tables and here is a Pivot answer. I've created some fake data using the RandBetween function and then copy and paste using values only. If you filter the pivot using the "Top 10" but change it to the "Top 1" you can get your answer with NO Formulas needed. See the attached.

5. ## Re: Select last value from a column with conditions

Thank you again for your support.

I tried to do: =INDEX(B1:B6,MAX(IF(MONTH(A1:A6)=2,ROW(B1:B6)))) the result was 43.2 and not 9.31.

I'm not understanding why...

Thank you again,

6. ## Re: Select last value from a column with conditions

Did you use the Ctrl + Shift + Enter?
I changed the INDEX to B:B, more robust.

7. ## Re: Select last value from a column with conditions

Try this

=INDEX(B1:B6,MATCH(10^308,IF(MONTH(A1:A6)=2,B:B)))

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Data Range
 A B C D 1 2/1/2015 9.12 9.31 2 2/10/2015 9.31 3 3/2/2015 0.98 4 5/11/2015 21.1 5 8/1/2015 54.2 6 10/29/2015 43.2

8. ## Re: Select last value from a column with conditions

Lookup will work too

=LOOKUP(2,1/(MONTH(A1:A6)=2),B1:B6)

9. ## Re: Select last value from a column with conditions

Dear all, I afraid that with this: MONTH(A1:A6)=2, the formula wont work with data like this:
1-Jan------1
4-Jan------2
1-Mar------3
15-Mar-----4
If February is chosen, the last date will be picked (4-Jan) and 2 is chosen. (my formula in #3 did)

10. ## Re: Select last value from a column with conditions

Thank you for your support, the formula using index actually works.

However, if I insert the month 4 for example, since there is no date with month four the formula returns an error. It is supposed to return the last value until 4/30/2015 for example, in this case the value from 3/2/2015 (0.98). I insert <= instead of equal...

You are helping me a lot. Thank you so much!

11. ## Re: Select last value from a column with conditions

Why I cannot insert also the year in the equation?

=INDEX(B:B;MAX(IF(AND(MONTH(A:A)=<4,YEAR(A:A)=<2015);ROW(B:B))))

12. ## Re: Select last value from a column with conditions

Originally Posted by fneves88
Thank you for your support, the formula using index actually works.

However, if I insert the month 4 for example, since there is no date with month four the formula returns an error. It is supposed to return the last value until 4/30/2015 for example, in this case the value from 3/2/2015 (0.98). I insert <= instead of equal...

You are helping me a lot. Thank you so much!
All suggested formulas are based on the real dates. If you using something else other than valid dates formulas will not work. I tried to understand what you were trying to explain but failed.

13. ## Re: Select last value from a column with conditions

Dear AlKey,

The formula works perfectly, I just improved it using <= instead of equal... What I need to filter is also per year, in other words, I need to check the last value from month 2, from year 2015, for example. I tried this formula but it is not working...

=INDEX(B:B;MAX(IF(AND(MONTH(A:A)=<4,YEAR(A:A)=<2015);ROW(B:B))))

14. ## Re: Select last value from a column with conditions

Year included would look something like this:
Formula:

AND does not work in array formulas as it just takes AND of the whole array. You have to use multiple IF's or multiplication instead.

15. ## Re: Select last value from a column with conditions

You are helping me a lot! Thank you so much!

Last question if you could please answer me. Imagine the next scenario:

A1: 2/1/2015 B1: 9.12 C1: In
A2: 2/10/2015 B2: 9.31 C2: Out
A3: 3/2/2015 B3: 0.98 C3: Loan In
A4: 5/11/2015 B4: 21.1 C4: In
A5: 8/1/2015 B5: 54.2 C5: In
A6: 10/29/2015 B6: 43.2 C6: In

How can I do the Sum of all values in B Column inserting a condition of month and year and C Column?

Example: I need to sum all the column in B that has the tag "In" and "Loan In" off all months until February 2015? Result: 9.12+9.31=18.43; April? 9.12+9.31+0.98=19.41. February 2014? Result = 0

Thanks a lot!!

16. ## Re: Select last value from a column with conditions

Is your calculation example wrong? 9.31 is "Out", no?
This is the formula I'm using:
Formula:

17. ## Re: Select last value from a column with conditions

Thank you so much for your help! I don't know how to thank you. Thank you!

''''''''''''

19. ## Re: Select last value from a column with conditions

And yes, the calculations that I sent to you were wrong, my mistake!

20. ## Re: Select last value from a column with conditions

Dear Jacc,

I was validating the formula more deeper and I found that when I introduce the year of 2016, month 2 for example, the last value is 9.31 and not 43.2. I'm been fighting to do this formula right but I don't understand why the formula is wrong, even change to

{=INDEX(B:B;MAX(IF(YEAR(A1:A6)<=E2;IF(MONTH(A1:A6)<=F2;ROW(B1:B6)))))}

Thank you again for your support..

21. ## Re: Select last value from a column with conditions

If you post a workbook with some sample data it will be much easier to help you.

22. ## Re: Select last value from a column with conditions

Sorry, here it is...

23. ## Re: Select last value from a column with conditions

Ah, I didn't understand what you meant at first. This should fix it. I fixed both formulas.
Formula:

Formula:

24. ## Re: Select last value from a column with conditions

I will try the formula tomorrow, is too late here. But can you explain me why you add 10?

Thank you again!

25. ## Re: Select last value from a column with conditions

When you use the & operator it doesn't look at the value of the month, just the character of the number. 2016&3 will then become a higher number than 2016&12. By adding 10 the order is restored since all months now have 2 digits.

26. ## Re: Select last value from a column with conditions

I'm having some troubles to pass this formula for google excel sheets. I'm still understanding why the same formula is not working well

27. ## Re: Select last value from a column with conditions

I'm having some troubles to pass this formula for google excel sheets. I'm still understanding why the same formula is not working well Dear Jacc, do you think that I can share with you my google excel sheet to help me?

Thank you so much!

28. ## Re: Select last value from a column with conditions

I believe my inbox was full, try again.

29. ## Re: Select last value from a column with conditions

Yes it was, no worries. My problem is, take into account the formula, when I select all values in a row (for example B:B or A:A) doesn't appear any value... :S

30. ## Re: Select last value from a column with conditions

Please see attachment for better understanding.

Once more time, thank you in advance, you have been excellent!

Find last for a month 4.xlsx

31. ## Re: Select last value from a column with conditions

That formula only works if the table is sorted with most recent dates at the bottom of the list. An empty cell is interpreted as 0 and hence the list is no longer sorted.
Also, I strongly advice against using array formulas with full length columns, it will make the worksheet slow because of all the millions of unnecessary calculations.

My advice is to use the Excel Table feature. This will automatically expand the table and the formulas when you add more data. In the attached workbook I have converted the formulas to Table references. I don't know if a similar feature is available in Google spreadsheets.
http://chandoo.org/wp/2009/09/10/data-tables/
http://www.contextures.com/xlExcelTable01.html

32. ## Re: Select last value from a column with conditions

Thanks for the update. There is no way to validate this formula only for example if the cell is not empty? :S

33. ## Re: Select last value from a column with conditions

Yeah that can be done. I still advice against using entire columns in formulas (apart from the first part in INDEX which I don't think adds to calculations).

Thank you!!!

35. ## Re: Select last value from a column with conditions

'''''''''''

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1