+ Reply to Thread
Results 1 to 5 of 5

Searching for the most recent (date) relevant entry in a table of data

  1. #1
    Registered User
    Join Date
    07-09-2020
    Location
    Brisbane, Australia
    MS-Off Ver
    11
    Posts
    5

    Searching for the most recent (date) relevant entry in a table of data

    Hi,

    Can't figure out how to find the most recent entry that matches my criteria.

    I have attached an example spreadsheet where the data table is in blue. The second table is the one I am creating, with everything in white already placed. What I am looking for is a formula to find the information in the green cells. That is, I want to search the data table for the matching employee number and have it display the leave balance that corresponds with the MOST RECENT date (column C). These dates are not sorted.

    Thanks in advance for any assistance!!
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Searching for the most recent (date) relevant entry in a table of data

    Try this:

    =INDEX($D$2:$D$28,MATCH(MAX(IF($B$2:$B$28=H2,$C$2:$C$28)),IF($B$2:$B$28=H2,$C$2:$C$28),0))

    Enter with Ctrl+Shift+Enter.

  3. #3
    Registered User
    Join Date
    07-09-2020
    Location
    Brisbane, Australia
    MS-Off Ver
    11
    Posts
    5
    Quote Originally Posted by Phuocam View Post
    Try this:

    =INDEX($D$2:$D$28,MATCH(MAX(IF($B$2:$B$28=H2,$C$2:$C$28)),IF($B$2:$B$28=H2,$C$2:$C$28),0))

    Enter with Ctrl+Shift+Enter.
    That’s perfect THANK YOU. Do you know how I would make it appear as blank if it can’t find anything? Rather than showing as #N/A ?

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Searching for the most recent (date) relevant entry in a table of data

    Add the IFERROR() function

    =IFERROR(INDEX($D$2:$D$28,MATCH(MAX(IF($B$2:$B$28=H2,$C$2:$C$28)),IF($B$2:$B$28=H2,$C$2:$C$28),0)),"")

  5. #5
    Registered User
    Join Date
    07-09-2020
    Location
    Brisbane, Australia
    MS-Off Ver
    11
    Posts
    5
    Quote Originally Posted by Phuocam View Post
    Add the IFERROR() function

    =IFERROR(INDEX($D$2:$D$28,MATCH(MAX(IF($B$2:$B$28=H2,$C$2:$C$28)),IF($B$2:$B$28=H2,$C$2:$C$28),0)),"")
    You’re a lifesaver! Thank you

+ 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] Formula for showing most recent data entry within cell
    By barneyrubble1965 in forum Excel General
    Replies: 1
    Last Post: 11-14-2019, 06:34 AM
  2. [SOLVED] Pulling The Date of the Most Recent Entry
    By mcadamshound in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-05-2015, 04:51 PM
  3. VLOOKUP Based on Date of Most Recent Data Entry
    By sky-raptor in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-11-2014, 12:11 PM
  4. [SOLVED] Code to move data from an entry table to a historical table by date.
    By rlh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2014, 11:04 AM
  5. [SOLVED] Formula for copying over relevant data from one table to populate another table
    By amasson in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-19-2013, 06:11 AM
  6. Single data entry cell for a range of cells from most to least recent entries?
    By orange108 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2011, 01:10 PM
  7. Excel Table Delete Duplicates Leaving Most Recent Entry
    By BuntyMac in forum Excel General
    Replies: 0
    Last Post: 03-02-2011, 11:10 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