# 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!

• ✭✭✭✭✭✭

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

• ✭✭✭✭

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?

• ✭✭✭✭✭✭

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

• ✭✭✭✭

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

• ✭✭✭✭✭✭
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!