Assistance with IF Formula
Why do I receive INVALID OPERATION error for the below formula:
=IF({Renewal Team Members Range 2} = "EMEA", {Renewal Team Members Range 4}, "")
I am trying to assign the value of Cell 1 Col 1 in Sheet 1 to Cell 1 Col 1 in sheet 2 if value of col 2 in sheet 1 is equal to EMEA.
for example
Sheet 1: All the users
User 1 — EMEA — Sales
User 2 — NA — Renewal
User 3 — EMEA - Sales
Sheet 2 - EMEAs users - Search for users based in EMEA and return their Username
Use 1
User 3
Answers
-
Hello @SomyFa,
Your formula has a value if true that is an entire range (bolded below). I suspect this is why you are getting the error. The value if true/false needs to refer to a discrete data point (or some formulaic manipulation of a range that can output as such).
=IF({Renewal Team Members Range 2} = "EMEA",
{Renewal Team Members Range 4}
, "")
You can use a lookup formula for this. It sounds like you want Sheet 2 to all values from Sheet 1 that match your search criteria (i.e., "EMEA"). I would try using INDEX(COLLECT()) for this as discussed here.
As an aside, if you don't want to output a value if false you don't need to add quotes, you can just omit it like this:
=IF({Renewal Team Members Range 2} = "EMEA",
{Renewal Team Members Range 4})
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!