New Contributor

# Need help with multiple IF formulas in a single cell

Hi, All!

I'm working on a sheet where I have a cell that I need multiple IF formulas in and I don't believe I'm doing it right and I'm not sure if it's possible. Here's what I'm working with:

• On one sheet I have 3 columns of cells (Category, Baseline, and Goal). There are 28 rows.
• On another sheet, I have those same 3 columns of cells, but only 5 rows.
• Under the "Category" column, each of the 5 rows is a data validation drop-down list made from the 28 "Category" choices on the first sheet. The user is supposed to pick 5 from the list of 28.
• Under the "Baseline" and "Goal" columns of the second sheet, I would like each of the 5 rows to automatically pull from the first sheet's corresponding "Baseline" and "Goal" cells, based on the chosen drop-down list "Category."

The following is an example of the multiple IF formula I'm going to try in a single cell which is a "Baseline" cell. In this example A1 represents one of the "Category" data validation drop-down lists on the second sheet. "Quarterly Goals" is the name of the first sheet. Keep in mind the multiple IF formula would go up to 28, but I only used up to 2 for this example.

=IF(A1='Quarterly Goals'!A1,'Quarterly Goals'!B1,IF(A1='Quarterly Goals'!A2,'Quarterly Goals'!B2,"0"))

Am I on the right track or should I be using a differently formula altogether? Please help; I'm a novice.

6 Replies

# Re: Need help with multiple IF formulas in a single cell

``=INDEX('Quarterly Goals'!B\$2:B\$28,MATCH(\$A2,'Quarterly Goals'!\$A\$2:\$A\$28,0))``

You can try INDEX and MATCH.

# Re: Need help with multiple IF formulas in a single cell

Sorry for the late reply. Thank you for the suggestion. I tried using the INDEX/MATCH feature and am still doing something wrong and keep getting the #SPILL! error code. I've been trying to play around with it and it's not working. Do you know if there is a way of doing the multiple IF formulas in a single cell?

# Re: Need help with multiple IF formulas in a single cell

You can apply a nested IF formula.

=IF(\$A2='Quarterly Goals'!\$A\$2,'Quarterly Goals'!B\$2,IF(\$A2='Quarterly Goals'!\$A\$3,'Quarterly Goals'!B\$3,IF(\$A2='Quarterly Goals'!\$A\$4,'Quarterly Goals'!B\$4,IF(\$A2='Quarterly Goals'!\$A\$5,'Quarterly Goals'!B\$5,IF(\$A2='Quarterly Goals'!\$A\$6,'Quarterly Goals'!B\$6,IF(\$A2='Quarterly Goals'!\$A\$7,'Quarterly Goals'!B\$7,IF(\$A2='Quarterly Goals'!\$A\$8,'Quarterly Goals'!B\$8,IF(\$A2='Quarterly Goals'!\$A\$9,'Quarterly Goals'!B\$9,IF(\$A2='Quarterly Goals'!\$A\$10,'Quarterly Goals'!B\$10,IF(\$A2='Quarterly Goals'!\$A\$11,'Quarterly Goals'!B\$11,IF(\$A2='Quarterly Goals'!\$A\$12,'Quarterly Goals'!B\$12,IF(\$A2='Quarterly Goals'!\$A\$13,'Quarterly Goals'!B\$13,IF(\$A2='Quarterly Goals'!\$A\$14,'Quarterly Goals'!B\$14,IF(\$A2='Quarterly Goals'!\$A\$15,'Quarterly Goals'!B\$15,IF(\$A2='Quarterly Goals'!\$A\$16,'Quarterly Goals'!B\$16,IF(\$A2='Quarterly Goals'!\$A\$17,'Quarterly Goals'!B\$17,IF(\$A2='Quarterly Goals'!\$A\$18,'Quarterly Goals'!B\$18,IF(\$A2='Quarterly Goals'!\$A\$19,'Quarterly Goals'!B\$19,IF(\$A2='Quarterly Goals'!\$A\$20,'Quarterly Goals'!B\$20,IF(\$A2='Quar
terly Goals'!\$A\$21,'Quarterly Goals'!B\$21,IF(\$A2='Quarterly Goals'!\$A\$22,'Quarterly Goals'!B\$22,IF(\$A2='Quarterly Goals'!\$A\$23,'Quarterly Goals'!B\$23,IF(\$A2='Quarterly Goals'!\$A\$24,'Quarterly Goals'!B\$24,IF(\$A2='Quarterly Goals'!\$A\$25,'Quarterly Goals'!B\$25,IF(\$A2='Quarterly Goals'!\$A\$26,'Quarterly Goals'!B\$26,IF(\$A2='Quarterly Goals'!\$A\$27,'Quarterly Goals'!B\$27,IF(\$A2='Quarterly Goals'!\$A\$28,'Quarterly Goals'!B\$28,"")))))))))))))))))))))))))))

In the example the above formula is entered in cell D2 and filled across range D2:E6.

# Re: Need help with multiple IF formulas in a single cell

just try with IFS FORMULA
=IFS(\$A2='Quarterly Goals'!\$A\$2,'Quarterly Goals'!C\$2,\$A2='Quarterly Goals'!\$A\$3,'Quarterly Goals'!C\$3,\$A2='Quarterly Goals'!\$A\$4,'Quarterly Goals'!C\$4,\$A2='Quarterly Goals'!\$A\$5,'Quarterly Goals'!C\$5,\$A2='Quarterly Goals'!\$A\$6,'Quarterly Goals'!C\$6,\$A2='Quarterly Goals'!\$A\$7,'Quarterly Goals'!C\$7,\$A2='Quarterly Goals'!\$A\$8,'Quarterly Goals'!C\$8,\$A2='Quarterly Goals'!\$A\$9,'Quarterly Goals'!C\$9,\$A2='Quarterly Goals'!\$A\$10,'Quarterly Goals'!C\$10,,true,"")

# Re: Need help with multiple IF formulas in a single cell

That didn't work either and I played around with the formula quite a bit. The error I kept getting is that Excel thinks I'm trying to do a formula because I'm using the "=" and I should use an apostrophe before the equal sign, which shouldn't be happening.

I updated and attached the spreadsheet you sent me to match the two real sheets from my document (with the proprietary information replaced by "subcategory"). Also, every cell in Column B of the Quarterly Goals sheet originally has a formula, but I removed them (again for proprietary purposes). On Sheet 2, Column L is being used to create the "Subcategory" drop-down lists and would be hidden in the final version of the Excel.

With this in mind: If in Sheet 2, cell A2, they choose L1 (AKA: Quarterly Goals sheet, cell A2) from the drop-down menu, how do I get Sheet 2, cell F2 to pull the Baseline for that selection (i.e., pull Quarterly Goals sheet, cell B2) and do the same for every subcategory in the drop-down list?

# Re: Need help with multiple IF formulas in a single cell

@Kimberlationism1775 Don't understand why INDEX/MATCH won't work. The #SPILL! error probably occurred because Excel referenced A2:E2 in the MATCH part of the formula, when you clicked on A2. That's due to the fact that you merged cell across columns A to D. Better to avoid that.

Enter this in F2 on Sheet2:

``=INDEX('Quarterly Goals'!B\$2:B\$37,MATCH(\$A2,'Quarterly Goals'!\$A\$2:\$A\$37,0))``

Copy down and across. It shall work.