+ Reply to Thread
Results 1 to 35 of 35

Select last value from a column with conditions

  1. #1
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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?

    Thank you again in advance,

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    Re: Select last value from a column with conditions

    Assuming it is sorted by date then try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This is an array formula and needs to be entered with Ctrl + Shift + Enter
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,654

    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. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

    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.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    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.
    Attached Files Attached Files

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    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
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Select last value from a column with conditions

    Lookup will work too

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

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,654

    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. #10
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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!
    Last edited by fneves88; 10-17-2015 at 01:25 PM.

  11. #11
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #12
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Select last value from a column with conditions

    Quote Originally Posted by fneves88 View Post
    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. #13
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #14
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    Re: Select last value from a column with conditions

    Year included would look something like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    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.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #16
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    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: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    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!

  18. #18
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    Re: Select last value from a column with conditions


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

  19. #19
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    Re: Select last value from a column with conditions

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

  20. #20
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #21
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    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. #22
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    Re: Select last value from a column with conditions

    Sorry, here it is...
    Attached Files Attached Files

  23. #23
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    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: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #25
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    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. #26
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #27
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #28
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    Re: Select last value from a column with conditions

    I believe my inbox was full, try again.

  29. #29
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #30
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #31
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    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
    http://www.jkp-ads.com/articles/Excel2007tables.asp
    Attached Files Attached Files

  32. #32
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    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. #33
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    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).
    Attached Files Attached Files

  34. #34
    Registered User
    Join Date
    10-17-2015
    Location
    Porto
    MS-Off Ver
    2011 (Mac OS)
    Posts
    17

    Re: Select last value from a column with conditions

    Thank you!!!

  35. #35
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    Re: Select last value from a column with conditions


    '''''''''''

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Select Only If No Other Cell is Greater Than X and All Other Conditions are Met
    By Gos-C in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-05-2015, 08:24 PM
  2. [SOLVED] select case multiple conditions
    By SPARKY347 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-09-2015, 04:36 PM
  3. [SOLVED] Select Entire Row When Two Conditions are met in a range
    By jagadeesh.rt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-20-2014, 12:09 PM
  4. Select and show a table with conditions
    By nicu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2014, 05:21 AM
  5. Replies: 4
    Last Post: 06-17-2013, 05:21 AM
  6. Formula to select value that satisfies conditions
    By bmind in forum Excel General
    Replies: 10
    Last Post: 04-03-2009, 11:07 AM
  7. More than 3 formatting conditions - select case?
    By HelenW in forum Excel Programming / VBA / Macros
    Replies: 34
    Last Post: 01-08-2009, 04:58 AM

Bookmarks

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