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.