Find the number in a second sheet and search for the latest date
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
Best Answer
-
Got it!!
This is how it works.
=MAX(COLLECT({Test1 Bereich 2}; {Test1 Bereich 1}; =Number@row))
Thanks for your help!
Regards
Christian
Answers
-
Is your Latest Date column in Sheet 2 an actual date type column, and is that data actually being stored as a date?
-
Yes it is a date and it is saved as a date.
-
MAX/COLLECT should have worked. Can you provide the formula you used and the error message?
-
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:
-
Try switching the ranges around so that the date range comes first and the comparison range comes before the criteria.
-
=MAX(COLLECT({Test1 Bereich 2}; =Number@row; {Test1 Bereich 1}))
If i do it so, I get "#INVALID OPERATION"
-
Got it!!
This is how it works.
=MAX(COLLECT({Test1 Bereich 2}; {Test1 Bereich 1}; =Number@row))
Thanks for your help!
Regards
Christian
-
Yes. That is what I meant. Just switching the ranges. Glad you got it sorted.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!