IF FUNCTION HELP

pbjrex 

What the formula

=IF(
   A2:A1146=(700047:700058:700059:700071:700086:700091)," Jackie",
IF(
   A2:A1146=(700053:700057:700063:700076:700079), "Frezell",
IF(...

does it compares A2:A1146 with combines ranges from row 700047 to row 700058 plus from row 700059 to row 700071, etc. If such rows ranges in formula are paired, Excel with dynamic arrays returns huge resulting array (which has no sense in any case) for which is not enough space in the sheet to place. If not paired as for the second IF formula Excel simply says you try to use incorrect formula.

Bit more practical will be the formula

=IF(
   ISNUMBER(MATCH(A2:A1146,{700047,700058,700059,700071,700086,700091},0)),"Jackie",
IF(
   ISNUMBER(MATCH(A2:A1146,{700053,700057,700063,700076,700079},0)),"Frezell",
IF(
   ISNUMBER(MATCH(A2:A1146,{700049,700051,700055,700062,700087,700090},0)),"Erika",
IF(
   ISNUMBER(MATCH(A2:A1146,{700056,700068,700085,700094,700095,700096,700097,700098},0)),"Arlesia",
IF(
   ISNUMBER(MATCH(A2:A1146,{700048,700050,700060,700061,700064,700070,700072,700077,700088,700089},0)),"Scott",
IF(
   ISNUMBER(MATCH(A2:A1146,{700052,700054,700065,700066,700080},0)),"Jody",
IF(
   ISNUMBER(MATCH(A2:A1146,{700103,700104,700106,700107,700108,700109,700110},0)),"Anthony",
IF(
   ISNUMBER(MATCH(A2:A1146,{700093,700099,700100,700101,700102,700105,700111,700113},0)),"Kandi",
IF(
   ISNUMBER(MATCH(A2:A1146,{700075,700078,700081,700084,700112,700114},0)),"Latesha",
IF(
   ISNUMBER(MATCH(A2:A1146,{700067,700069,700073,700074,700082,700083,700092},0)),"Jenn",""
))))))))))

which on dynamic array (DA) Excel returns correct results into the column B.

If put constants into the helper range

same formula could be transformed to

=IF(
   ISNUMBER(MATCH(A2:A1146,$H$1:$U$1,0)),"Jackie",
IF(
   ISNUMBER(MATCH(A2:A1146,$H$2:$U$2,0)),"Frezell",
IF(
   ISNUMBER(MATCH(A2:A1146,$H$3:$U$3,0)),"Erika",
IF(
   ISNUMBER(MATCH(A2:A1146,$H$4:$U$4,0)),"Arlesia",
IF(
   ISNUMBER(MATCH(A2:A1146,$H$5:$U$5,0)),"Scott",
IF(
   ISNUMBER(MATCH(A2:A1146,$H$6:$U$6,0)),"Jody",
IF(
   ISNUMBER(MATCH(A2:A1146,$H$7:$U$7,0)),"Anthony",
IF(
   ISNUMBER(MATCH(A2:A1146,$H$8:$U$8,0)),"Kandi",
IF(
   ISNUMBER(MATCH(A2:A1146,$H$9:$U$9,0)),"Latesha",
IF(
   ISNUMBER(MATCH(A2:A1146,$H$10:$U$10,0)),"Jenn",""
))))))))))

On DA Excel above formulas spill result down. On pre-DA Excel they could be used if select B2:B1145 and in formula bar enter the formula as array one, i.e. with Ctrl+Shift+Enter. Or, alternatively, use absolute reference in column A range ($A$2:$A$1146), enter formula in B2 and drag down.

To return names we may use in D2 regular formula

=IFNA(IF(A2="","",
     INDEX($G$1:$G$10,
          MATCH(SUMPRODUCT(($H$1:$Q$10=A2)*ROW($H$1:$Q$10)),
          ROW($G$1:$G$10),
          0))),
"")

and drag it down.

Of course, already suggested VLOOKUP always works.

What you've written is a dynamic array formula--If excel is giving you a spill error, your formula wants to return a range and there are values in adjacent cells preventing it from doing so. Clear up some space around the cell your formula is in.

Do you mean that Jackie can be associated with 700047 or 70058, etc?

If so, you want to pop those inside the OR function. Also, remove the range and just leave A2 and drag the formula down column B.

IF(OR($A2=70047, $A2=70058), "Jackie",IF(OR($A2=70053,$A2=70057),"Frezell",IF(... this will solve your issue of the array formula and prevent spill errors. When you drag the formula down column B, A2 will change to A3 in the next row.

pbjrex 

With all that logic you might be best off creating a lookup table then run a VLOOKUP off of it.