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

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 06/26/24

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!