How to index /match that has multiple dates- but I want the latest one

I have read so many index/match, max/collect, and index /collect questions, and all of them seem to be just a little different than what I need, and I can't figure this out.

So I have two sheets, one data sheet that I am pulling from based on criteria from another sheet. Except the data sheet has multiple entries and I want to pull just the date of most current or latest one.

This is the sheet I would reference as the FTO number I want to find that data for, this will come from the next sheet below.

So, I want to pull the latest date based on the FTO number. I would normally just do this with an index/match formula which I am very familiar with, but to only pull the latest date is where I am stumped.

Any advice on how to get what I am looking for on this?

Find more posts tagged with

Formulas

help

Accepted answers

All comments

Jgorsich

Without having all of your sheets in front of me - or more detailed screenshots - it is hard to make this efficient, BUT… here are a couple of methods that can work:

Helper columns (presumes your entry of stuff is in order)
1. On your sheet with your FTO and dates, add a helper column that is a countifs for that FTO number and date values equal to or greater than the date for what was entered - this will basically give you an ordered prioritization of FTO numbers and the one with a "1" will always have the latest date. Modify that formula to append the result to your FTO number with a hyphen, so you've got a column of FTO numbers and next to it you've got a column with the same FTO number but each one ends with "-x" where x is the result of your formula.
2. Once that is done, in your index(match()) formula, rather than searching your column of FTO numbers, search your new helper column for "FTO-1" (replacing FTO with your number) and you'll be good to go.
nested collects
1. Start with a collect of all dates that match that FTO, determine the max, make a collection of all dates that have that FTO and are the max, return the first entry.

Method 2 will avoid the need for a helper column, but be more complex and harder to trouble shoot and maintain in the future.

Nick Amsler

So the data isn't always in order, so I am just looking to grab the data based on the most recent date. I'm guessing the max/collect is the way to go. Do you or anyone know how to format that in conjunction with an index/match formula to pull it from one sheet to another?

I need to do this to pull the latest date itself, and then also pull another piece of data from the same row (on that latest date).

I appreciate the help with this!

Jgorsich

@Nick Amsler - there would be a few ways to do that, with sets of collects that pull the info and basically get it down to where you only have 1 entry and then output that entry, but it would be a bit of a bear. If you've got to pull multiple pieces of information following a complicated collect statement I'd recommend that you add another helper column to your data sheet with the FTO and dates that is basically just a unique identifier of a row (the easiest would just be row number - look up any answer with "row@row" in it and you'll find a handful of ways to do this). Then, when you do your original collect (method 2), make sure you are ultimately pulling that unique identifier number rather than a date or FTO number. Output this unique identifier number to a helper column in your formula sheet - then you can just use index(match()) to pull any information you want without having multiple copies of your collect() formulas.

This would BASICALLY be the same thing as just collecting specifically the information you want each time, but will be slightly easier to troubleshoot.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.