Can you find the MAX value from the Index in an INDEX MATCH formula?

Hi, I'm using to the Index Match formula to pull a date from a column if the emails match but I want if for the formula to pull the MAX date as there are multiple entries where the emails would match. Is there a way to do that? Thank you for you help!

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Kavs

    One approach is to use a MAX/COLLECT. I'll assume your data is cross referenced. I include criteria to make sure all records have dates values.

    An example of the syntax is as follows. You will need to create this within your sheet using your actual references.

    =MAX(COLLECT({Source sheet Date column}, {Source sheet Date column}, ISDATE(@cell), {Source sheet email column}, email@row))

    Does this work you?

    Kelly

  • Kavs
    Kavs ✭✭✭✭

    Yes! Thank you! Question: Some cells have "N/A" and when this formula is used those cells don't populate anything remain blank. Is there a way to add that criteria into the formula?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Sure. What field has N/A and what would you like to happen when it occurs?

  • Kavs
    Kavs ✭✭✭✭

    The date field in some cells have N/A so would like the "N/A" to populate when using the formula.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 08/05/22

    Hey

    Again I will assume you are working across sheets (vs your Max/Collect formula is on the same sheet with all the data). If my assumption is incorrect, we will have to re-format the formula below.

    =IF(COUNTIFS({Source sheet Date column, "N/A",{Source sheet email column}, email@row)>0, "N/A",MAX(COLLECT({Source sheet Date column}, {Source sheet Date column}, ISDATE(@cell), {Source sheet email column}, email@row)))

    Does this work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!