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



  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    Try this,

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

  • 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.
    Genevieve P. Employee Admin

    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?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!