INDEX MATCH to return last matching row value

Pierre Mostert

Good day,

I would like to return the last matching row using INDEX MATCH for unsorted data.

In the example below, I would like to return the last Date Log - 2020/05/26 for NODE B?

To return the first Date Log, I use the following formula that works:

=INDEX([Date Log]1:[Date Log]6; MATCH([Search value]1; NODE1:NODE6; 0))

Some assistance will be highly appreciated.

Regards,

Pierre

Paul Newcome

Ok. So I am not sure why it is working the way it is generating those particular errors, but here is a fix...

=IF(COUNTIFS(NODE:NODE; [Search value]@row) > 0; INDEX([Date Log]:[Date Log]; MAX(COLLECT([Row #]:[Row #]; NODE:NODE; [Search value]@row))))

Paul Newcome

If you are looking for the latest date based on the NODE, you can use something along the lines of...

=MAX(COLLECT([Date Log]:[Date Log]; NODE:NODE; [Search value]@row))

Pierre Mostert

Hi Paul

Thanks for your response.

I tried it and it works great for the use case I provided.

But, when the date for the latest entry is smaller, it doesn't work. See screenshot below. Is there a way to modify the function to cater for such a case?

Also I will need to include INDEX as I am referencing an external sheet to return the latest entry for the Node.

Regards,

Pierre

Paul Newcome

Oh. So you are wanting the entry that is lowest in the list. Ok. My apologies. I assumed the "latest entry" was based on the date.

If you are wanting the furthest down the column, I would suggest adding in a "helper" column [Row #] (text/number type) where we can enter a formula into row 1 and dragfill it down the rest of the rows to replicate the row number.

=COUNTIFS(NODE$1:NODE@row, OR(@cell = "", @cell <> ""))

Then we can use

=INDEX([Date Log]:[Date Log], MAX(COLLECT([Row #]:[Row #], NODE:NODE, [Search value]@row)))

Pierre Mostert

Hi Paul

Thanks this works great. Just one more issue I need help with. In the case of Node E for example being removed from the range, I receive a #DATE EXPECTED Error.

How do I force this to be kept BLANK if the search value cant be found? See example below:

Regards,

Pierre

Paul Newcome

We can use an IFERROR

=IFERROR(INDEX([Date Log]:[Date Log], MAX(COLLECT([Row #]:[Row #], NODE:NODE, [Search value]@row))), "")

Pierre Mostert

I tried that, but I still get the same error/result:

Regards,

Paul Newcome

No worries. Some errors can be "fixed" by the IFERROR and some can't. I just can never remember which is which. Give this one a go...

=IF(ISDATE(INDEX([Date Log]:[Date Log], MAX(COLLECT([Row #]:[Row #], NODE:NODE, [Search value]@row)))), INDEX([Date Log]:[Date Log], MAX(COLLECT([Row #]:[Row #], NODE:NODE, [Search value]@row))))

Pierre Mostert

I think we almost there. But now I am getting the following:

It works fine when the Node E is present.

Paul Newcome

That's odd. Let's do this one step at a time to make sure all parenthesis and whatnot are in the correct spots...

=IF(ISDATE(index_formula); index_formula)

That is our desired syntax. We want to drop the index formula into each of those two sections.

INDEX([Date Log]:[Date Log]; MAX(COLLECT([Row #]:[Row #]; NODE:NODE; [Search value]@row)))

=IF(ISDATE(INDEX([Date Log]:[Date Log]; MAX(COLLECT([Row #]:[Row #]; NODE:NODE; [Search value]@row)))); INDEX([Date Log]:[Date Log]; MAX(COLLECT([Row #]:[Row #]; NODE:NODE; [Search value]@row))))

So it looks like everything is in place.

What gets me is that the Index formula on its own should actually be throwing a #NO MATCH error since there is no letter E in the NODE column.

I am going to do some testing and see what I can figure out. I'll get back to you. If you happen to get it working in the meantime, please revisit this thread and let us know.

Paul Newcome

Did you double check that the [Date Log] column set as a Date type?

Pierre Mostert

Yes I checked it. I also checked that the 'formula column' is a date type.

Pierre Mostert

Hi Paul

I just tested it piece by piece. But still no joy.

Yes I would also think it should return a #NO MATCH if no match can be found.

I will let you know if I get the formula right.

Thanks again for the assistance

Paul Newcome

Ok. So I am not sure why it is working the way it is generating those particular errors, but here is a fix...

=IF(COUNTIFS(NODE:NODE; [Search value]@row) > 0; INDEX([Date Log]:[Date Log]; MAX(COLLECT([Row #]:[Row #]; NODE:NODE; [Search value]@row))))

Pierre Mostert

Hi Paul

Thanks for all the assistance. Yes this works 100%

I will keep you posted if an alternative is found.

Much appreciated,

Regards,

Pierre

Paul Newcome

Great! Happy to help. 👍️