Index, Match and Max Formula

[Darkcloud617]

Hello,

I have a working formula but I am bit confused on including a MAX part of the formula. Essentially I am needing this to search the list for the latest date and return that as opposed to just the first one it finds in the list that is a match.

Please Login or Register  to view this content.

Again, this does exactly what its supposed to but it is returning the first Match found as opposed to the newest. Any ideas on modifying a code like this to add in a MAX function. I would really appreciate any assistance.

[cubangt]

Apply the MAX around the lookup_value(but instead of just M2, do the max on the column or range in M)

[Darkcloud617]

Thank you. I attempted to follow your instructions but I am getting a #Name error. I must be misunderstanding:

Please Login or Register  to view this content.

[tanasedn]

Can you, please, upload a sample file?

[cubangt]

sample file would help trouble shoot the issue, the steps are above in the yellow banner.

May need to add another formula within the above to find the max date for that record you trying to find..

[Darkcloud617]

Thank you for the responses. I attached an example workbook to this email. Please let me know if I can provide any other info.

[wk9128]

The original formula is written like this =INDEX($I:$I,AGGREGATE(14,7,ROW($2:$102)-1/($C$2:$C$102=M2),1)), I read it POST#8 Reply, re-examined my
own results and still found that this formula made a small mistake. Looking at the results in detail, there will be problems, so I thought of the
simpler and correct method as follows, and also use the AGGREGATE function

Cell N2 formula , drag down

Array formula

[Darkcloud617]

Worked like a charm. I've never actually used an Aggregate formula like this before so I definitely learned something after researching it. Thank you so much.

[wk9128]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'.

I found a problem

1. when Test17, the return of 2021/7/14 is correct, but the return of 2021/7/12 is incorrect
2. when Test22, the return of 2021/7/14 is correct, but the return of 2021/7/12 is incorrect
3. when Test24, the return of 2021/7/9 is correct, but the return of 2021/7/7 is incorrect

POST#7 has updated the correct formula

[FlameRetired]

wk9128 please don't edit your posts after they have already had response (#7). Though you mention it later it still confuses the flow of the conversation. Instead post corrections in a new post and refer to the post that is in error.

Thank you.

[FlameRetired]

Another way.

Please Login or Register  to view this content.

[wk9128]

Sorry @FlameRetired , the original problem has been placed back to POST#7 and the correct solution has been revised at the same time