Assistance with IF Formula

SomyFa

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

SoS | Dan Palenchar

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})