Need help with multiple IF formulas in a single cell

Copper Contributor

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

@Kimberlationism1775 

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

You can try INDEX and MATCH.

quarterly goals.JPG 

@OliverScheurich 

 

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?

@Kimberlationism1775 

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.

 

nested if.JPG

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,"")

@OliverScheurich 

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?

@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.