+ Reply to Thread
Results 1 to 7 of 7

Excel formula to get MAX from just a part of data

  1. #1
    Registered User
    Join Date
    11-28-2014
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    2

    Excel formula to get MAX from just a part of data

    Hello


    I'm having a problem with getting the right formula in excel 2010. I've got a table with few columns, but i'm only going to use data for 2 of them. In one of them, i've got NAMES, in another - DATES (in a format of YYYYMMDD). In another sheet i use a field, where i put the NAME, and i want to get the last (most recent) date for that name (so, as it's a number, i can just get the MAX out of it). The number of the name occurance varies, sometimes it's none (where it'll show that there's none, but that's a simple function), sometimes it can get to couple dozens.

    I know, how to make it in SQL, more or less. It'd be something like:
    SELECT MAX(date_column) FROM table WHERE NAME = C5;
    C5 being the field, where i put the NAME for search.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,917

    Re: Excel formula to get MAX from just a part of data

    pls attach sample file
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    11-28-2014
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    2

    Re: Excel formula to get MAX from just a part of data

    I don't know why, but i get the error, when trying to upload a file, so here's a sample table. I want a formula, that when i type "mary" in a "search cell", i get the response 20141126, when i type "Zoe", I get 20141119, when i type TED, (he's not in the table), then i get a blank cell/null/something like that.
    Name(Column A) Date(Column B)
    Mary 20141120
    Mary 20141126
    John 20141101
    John 20141105
    John 20141115
    Steve 20141116
    Zoe 20141109
    Zoe 20141110
    Zoe 20141113
    Zoe 20141119

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,244

    Re: Excel formula to get MAX from just a part of data

    Hi, to attach your workbook, click "Go Advanced" button and then find Paperclip Button to attah the file.

    Thanks

  5. #5
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,454

    Re: Excel formula to get MAX from just a part of data

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


    ...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. Press F2 on that cell and try again.
    Click just below left if it helps, Booşathì

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,917

    Re: Excel formula to get MAX from just a part of data

    =IFERROR(LOOKUP(2,1/(A1:A10=C1),B1:B10),"Not Found")
    see the attached file
    Attached Files Attached Files

  7. #7
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Excel formula to get MAX from just a part of data

    Non-array solution

    =MAX(INDEX((A2:A11="Zoe")*B2:B11,0),0)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ 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. Replies: 3
    Last Post: 03-25-2014, 11:05 AM
  2. Replies: 16
    Last Post: 08-19-2013, 12:10 PM
  3. Formula to extract part data from a cell
    By Notters in forum Excel General
    Replies: 1
    Last Post: 05-27-2010, 10:31 AM
  4. Formula to help with picking part of cell data
    By gurj_sandhu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2008, 12:52 PM
  5. How do I create a formula using part of the data in 1 cell?
    By SMC0890 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2006, 03:48 PM

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