+ Reply to Thread
Results 1 to 11 of 11

Formula to Look for value then for latest date then show value in third column

  1. 07-16-2017, 02:44 PM #1
    Jamidd1
    Jamidd1 is offline
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Formula to Look for value then for latest date then show value in third column

    Hi Exlcel Forum,

    I have 2 sheets one with a overall table and one with an individual.
    I am trying to make the table on the first sheet extract information from the second.
    On the table on the second sheet i have a exam title column, a exam date column and a grade column

    I need a formula (maybe an index match formula) that look in the first column for a specific word and then looks for the most recent date and then returns the result in the final column.

    I have been messing around but have not managed to figure it out yet.
    I have attached a sample workbook as this will probably help with my rubbish explaining! haha

    Thanks

    Jamidd
    Attached Files Attached Files
    Last edited by Jamidd1; 07-16-2017 at 03:11 PM.
    Reply With QuoteRegister To Reply
  2. 07-16-2017, 03:06 PM #2
    Jamidd1
    Jamidd1 is offline
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Formula to Look for value then for latest date then show value in third column

    I thought this might work

    Please Login or Register  to view this content.
    But i just get N/A
    Reply With QuoteRegister To Reply
  3. 07-16-2017, 03:20 PM #3
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,311

    Re: Formula to Look for value then for latest date then show value in third column

    in D2

    =MAX(IF((Table1[Exam Qualification]='Data Table'!D1)*(Table1[Date Of Exam]),Table1[Date Of Exam]))

    ...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.
    Reply With QuoteRegister To Reply
  4. 07-16-2017, 03:22 PM #4
    Jamidd1
    Jamidd1 is offline
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Formula to Look for value then for latest date then show value in third column

    Thanks John for you quick reply, although this returns the latest date which is half way there but instead of the latest date i need the latest grade if that makes sense?

    Thanks Again.
    Reply With QuoteRegister To Reply
  5. 07-16-2017, 03:39 PM #5
    Jamidd1
    Jamidd1 is offline
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Formula to Look for value then for latest date then show value in third column

    So i guess first i need it to look for what is in D1 then look for the latest date and then return the value in the Achieved Grade column
    Reply With QuoteRegister To Reply
  6. 07-16-2017, 04:00 PM #6
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,311

    Re: Formula to Look for value then for latest date then show value in third column

    TRy

    =INDEX(Table1[Achieved Grade],MATCH('Data Table'!D1&MAX(IF((Table1[Exam Qualification]='Data Table'!D1)*(Table1[Date Of Exam]),Table1[Date Of Exam])),Table1[Exam Qualification]&Table1[Date Of Exam],0))

    ...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.
    Reply With QuoteRegister To Reply
  7. 07-17-2017, 05:28 PM #7
    Jamidd1
    Jamidd1 is offline
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Formula to Look for value then for latest date then show value in third column

    Thanks John,

    This seems to work perfectly!

    I will mark this as solved and if any problems arise will come back here.

    Thanks again
    Reply With QuoteRegister To Reply
  8. 11-07-2017, 05:54 PM #8
    Jamidd1
    Jamidd1 is offline
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Formula to Look for value then for latest date then show value in third column

    Quote Originally Posted by JohnTopley View Post
    TRy

    =INDEX(Table1[Achieved Grade],MATCH('Data Table'!D1&MAX(IF((Table1[Exam Qualification]='Data Table'!D1)*(Table1[Date Of Exam]),Table1[Date Of Exam])),Table1[Exam Qualification]&Table1[Date Of Exam],0))

    ...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.
    I was wondering if anyone would know how to adapt this formula to not look for the date but search by course name and return the highest grade achieved?
    Reply With QuoteRegister To Reply
  9. 11-08-2017, 06:29 PM #9
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,611

    Re: Formula to Look for value then for latest date then show value in third column

    Assuming that the grades are P and F (as in the file attached to post #1) and that P is 'higher' than F the following formula may be pasted into D2 (Data Table sheet) and dragged across to G2:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  10. 11-11-2017, 03:34 PM #10
    Jamidd1
    Jamidd1 is offline
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: Formula to Look for value then for latest date then show value in third column

    Thats perfect thanks Jetemc!

    So if the grades are A Through to D would it just be a matter of adapting it like s

    Please Login or Register  to view this content.
    Thanks for you help so far!
    Reply With QuoteRegister To Reply
  11. 11-12-2017, 11:16 AM #11
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,611

    Re: Formula to Look for value then for latest date then show value in third column

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

    Let us know if you have any questions.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to match last date entered in latest column
    By Jay_Bannister in forum Excel General
    Replies: 5
    Last Post: 04-01-2016, 08:24 AM
  2. VBA- Filter Pivot table based on latest and 2nd latest date in column
    By ziyan89 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 12-29-2015, 11:18 AM
  3. [SOLVED] Show latest date in a column
    By guy13 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-06-2015, 04:51 AM
  4. [SOLVED] Check Contact Name in Column, Return Latest Date and 2 Cells Next to the Latest Date
    By nKife in forum Excel General
    Replies: 8
    Last Post: 04-08-2015, 10:10 PM
  5. Need help to show only the latest date
    By stuhico09 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-19-2015, 04:00 AM
  6. formula at the top of the sheet to show the latest figure in column
    By karloss in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2009, 12:30 PM
  7. [SOLVED] show latest date input only
    By Nigel in forum Excel General
    Replies: 1
    Last Post: 12-01-2005, 08:00 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1