Nested formula with sheet references help

I need a formula that will:

look up a name listed on sheet #1 on Sheet #2 and pull the most recent date they completed training. So far I have tried two formula combinations and neither has worked.

=IF({Training Data Range 1 - Name} = Name@row, MAX({Training Data Range 2 - Date})) #INVALID OPERATION

=VLOOKUP(Name@row, {Training Data Range 3}, MAX({CIV CJIS Training Data Range 2 - Date}), false) #INVALID ARGUMENT SET

Find more posts tagged with

Formulas

Accepted answers

All comments

Sameer Karkhanis

Try this,

=MAX(COLLECT({Training Data Range 2 - Date}, {Training Data Range 1 - Name}, Name@row))

collettee

So I put that in and I'm no longer receiving an error message, but it's not pulling the date in--Just blank.

Genevieve P.

Hi @collettee

Is it possible that either the "Name" isn't finding a match across your sheets, or that the there is a blank cell associated with that name instead of one with a date?

Try using a COUNTIF formula to check and make sure that the "Name" is matching:

=COUNTIF({Training Data Range 1 - Name}, Name@row)

If this is returning 0, then the formula can't find a match for that Name. Make sure that the spelling is exactly the same across sheets, including spaces.

If the number is correct in this COUNTIF, check to make sure that your {Training Data Range 2 - Date} is looking at the correct column, that the column is set to date type, and that there are dates associated with that person.

If none of this has helped, can you provide a screen capture of your source sheet, but block out sensitive data?

Cheers,

Genevieve

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.