Forum Discussion
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.
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?
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.
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.
