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

Kavs

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!

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

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

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

Kavs

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

Kelly Moore

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