Using COLLECT within multiple IF function
Hi,
I have an issue with a formula where I'm trying to use COLLECT within IF, but the formula returns "#INVALID COLUMN VALUE" and I can't figure out why.
I have a sheet where I would like to return the Lead Time of each product based on the product name and the mode of transport.
The information of Lead Times I have in another sheet (which I use as reference) where the product name and the different lead times are stated in one row, so the formula needs to pick up which column to use based on the product name.
What I want to achieve is, when someone fills in the product name and the mode of transport in their respective columns, the lead time is returned automatically in the Lead Time column.
So far I have this:
=IF([Mode of Transport]@row = "Air", COLLECT({Reference pricing list Range 1}, {Product Description}, [Product Description]@row), IF([Mode of Transport]@row = "Sea", COLLECT({Reference pricing list Range 2}, {Product Description}, [Product Description]@row), ""))
Maybe if IF and COLLECT is not even the right approach, but it seemed logical for me.
Thanks for any tips in advance!
Answers
-
Hi @Dora Berky
An IF( statement that uses COLLECT is a great idea! However the COLLECT function needs to be wrapped within another function, such as MIN or MAX or, what I would suggest, JOIN.
Try this:
=IF([Mode of Transport]@row = "Air", JOIN(COLLECT({Reference pricing list Range 1}, {Product Description}, [Product Description]@row)), IF([Mode of Transport]@row = "Sea", JOIN(COLLECT({Reference pricing list Range 2}, {Product Description}, [Product Description]@row))))
If there are multiple cells that meet the criteria of [Product Description]@row in your Range 1 or Range 2 then you will have multiple values squished together returned. Let me know if that's what you're seeing and I'm happy to help come up with some other solution! In that instance we could maybe use a SUMIF instead.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I have a formula used twice COLLECT the same range and pop "#Invalid operation" error. Is COLLECT has some limitation when use? My formula is like this: “=IF(MAX(COLLECT([date1]:[date1], Cell:Cell, "Cell-1")) > 0, MAX(COLLECT([date1]:[date1], Cell:Cell, "Cell-1")), 0)”
+ comment: I`ve found the issue that the result of my MAX() is a date type, it can not compare with number value 0 directly. I replace 0 with another date type value for example DATE(1990,1,1) then it`s ok now.
-
Hi @chen
Thanks for following-up with what you found out! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!