IFS Formula

[Adam Bruno]

I am currently trying to write a IFS formula so that when one item is selected from a list, another cell will generate the owner of the item selected from the list. The formula doesn't show any errors, but only works with a few entries (see below); even after copying and pasting items from the list itself.

PLEASE HELP!!

Formula:

=IFS(G10={Sheet2!F2:F22},"OWNER 1",G10={Sheet2!G2:G15},"OWNER 2",G10={Sheet2!H2:H16},"OWNER 3",G10={Sheet2!I2:I43},"OWNER 4",G10={Sheet2!J2:J13},"OWNER 5", G10={Sheet2!K2:K3},"OWNER 6")

Entries in F2, G2, H2, I2, J2, K2 all work properly, along with a few other random ones, but any others show a "#N/A" error. I have also tried rewriting the formula using IF before each rule and ending with multiple closed parentheses.

Adam

[#2]

Hi Adam,

IMHO, your formula shall not work. Sheet2!F2:F22 is array itself and {} are used for the array constants, combination of the is incorrect.

Anyway, what's your initial logic

if G2 is any of Sheet2!F2:F22 then "Owner 1" else

if G10 is any of Sheet2!G2:G15 then "Owner 2" else

etc.

Correct? If so that could be like

=IFS(COUNTIF(Sheet2!F2:F22,G10),"OWNER 1",
COUNTIF(Sheet2!G2:G15,G10),"OWNER 2",
COUNTIF(Sheet2!H2:H16,G10),"OWNER 3",
COUNTIF(Sheet2!I2:I43,G10),"OWNER 4",
COUNTIF(Sheet2!J2:J13,G10),"OWNER 5",
COUNTIF(Sheet2!K2:K3,G10),"OWNER 6",
TRUE,"No owner")