• 543K Members
• 5,388 Online
• 647K Conversations

## IF FUNCTION HELP

Occasional Contributor

# IF FUNCTION HELP

Here's what I'm trying to accomplish but either get a spill error or just a general problem with the formula error. I'm basically asking that from the data in column A2:A1146 if it equals a certain number to put a name in column B.

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

8 Replies
Highlighted

# Re: IF FUNCTION HELP

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

Highlighted

# Re: IF FUNCTION HELP

@Patrick2788 surprisingly I do more If functions than I have VLookup. How would I initiate that? And thank you!

Highlighted

# Re: IF FUNCTION HELP

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.

Highlighted

# Re: IF FUNCTION HELP

Sample attached. Hope this helps. Enjoy your weekend!

Highlighted

# Re: IF FUNCTION HELP

@shawb Thank you! I'll try this now!

Highlighted

# Re: IF FUNCTION HELP

any advice it there are too many arguments?
Highlighted

Thank you!
Highlighted

# Re: IF FUNCTION HELP

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.

Related Conversations