Find the number in a second sheet and search for the latest date

ChrisNHS

Hey community,

I have to sheets:

In sheet 1, I will show the latest date for a special "string". This date I need to search in sheet 2.

See picture below.

In sheet 1: For the number 43KA123456 it gives me back the latest date from sheet 2 for that number. If the number is not found, it gives me back "not found".

Sheet 1 has approx 10000 lines and sheet 2 approx 8000 lines.

I try it with =MAX(Collect()), but this doen´t work. But it could also have been a syntax problem.

Do you have an good idea how to handle this.

Kind regards

Christian

ChrisNHS

Got it!!

This is how it works.

=MAX(COLLECT({Test1 Bereich 2}; {Test1 Bereich 1}; =Number@row))

Thanks for your help!

Regards

Christian

Paul Newcome

Is your Latest Date column in Sheet 2 an actual date type column, and is that data actually being stored as a date?

ChrisNHS

Yes it is a date and it is saved as a date.

Paul Newcome

MAX/COLLECT should have worked. Can you provide the formula you used and the error message?

ChrisNHS

Test1 Bereich 1: sheet 2 - number

Test1 Bereich 2: sheet 2 - latest date

=MAX(COLLECT({Test1 Bereich 1}; {Test1 Bereich 2}; Number@row))

I guess it is not correct how I use it, the formular doen´t show an error:

Paul Newcome

Try switching the ranges around so that the date range comes first and the comparison range comes before the criteria.

ChrisNHS

=MAX(COLLECT({Test1 Bereich 2}; =Number@row; {Test1 Bereich 1}))

If i do it so, I get "#INVALID OPERATION"

ChrisNHS

Got it!!

This is how it works.

=MAX(COLLECT({Test1 Bereich 2}; {Test1 Bereich 1}; =Number@row))

Thanks for your help!

Regards

Christian

Paul Newcome

Yes. That is what I meant. Just switching the ranges. Glad you got it sorted.