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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!