# Better alternative to large nested IF formula in Excel?

I have a list of food items, each associated with 3 numbers. When I select that food item via dropdown menu (on the left side of the screenshot, where I have selected "steak"), I have it auto-populate the cells below it with those 3 associated numbers. However, I currently do this via a massive nested IF formula (as seen in screenshot).

I'm wondering what some more efficient ways to do it could be? Preferably a way that would allow me to add as many food items as possible. Excel tells me the formula is too long, so I currently can't add anymore food items.

• Is using a VBA-function an option? – IQV Apr 20 '17 at 6:03
• To be honest I didn't think someone could make it without using VLOOKUP. Now I know it's possible to use IF to substitute VLOOKUP. And yes, I know the formula limit. It's just cool to know. – Vylix Apr 20 '17 at 6:19

What you are looking for is VLOOKUP

Put these formulas below the red rows (which you used to input Steak)

=VLOOKUP(I4, \$V\$5:\$Y\$8, 2, FALSE)
=VLOOKUP(I4, \$V\$5:\$Y\$8, 3, FALSE)
=VLOOKUP(I4, \$V\$5:\$Y\$8, 4, FALSE)

Note: I'm assuming the steak row is row 4. I'm also assuming the Nutritional table ends at row 8. Adjust the \$Y\$8 part if your table has more.

Or INDEX MATCH variation

=INDEX(\$W\$5:\$W\$8, MATCH(I4, \$V\$5:\$V8, 0))
=INDEX(\$X\$5:\$X\$8, MATCH(I4, \$V\$5:\$V8, 0))
=INDEX(\$Y\$5:\$Y\$8, MATCH(I4, \$V\$5:\$V8, 0))

You can use VLOOKUP just fine, but INDEX MATCH is safer if you want to add column(s) in the nutritional table in the future.

EDIT:

If you got error because you have your food row is blank right now, wrap it up with IFERROR or IF(ISBLANK)

=IFERROR(VLOOKUP( ... ), 0)
=IF(ISBLANK(I4), 0, VLOOKUP( ... ))
• You should add ", FALSE" after the look reference in your VLOOKUP's: =VLOOKUP(I4, \$V\$5:\$Y\$8, 2, FALSE). Some international users may also need to change the comma separator to a semi colon (;) =VLOOKUP(I4; \$V\$5:\$Y\$8; 2; FALSE) - not relevant for this user, but good info if someone else stumbles on it in a few months time. – Kevin Anthony Oppegaard Rose Apr 20 '17 at 6:09
• Thanks a lot guys. I just came across the VLOOKUP and INDEX MATCH tactics before I posted this question last night. Will definitely be trying this and let you know how it goes! – amota Apr 21 '17 at 0:14
• Oh my god. Thank you so much haha. I'm now using IF(ISBLANK(), 0, INDEX(...MATCH(...))) and it works SO WELL! So much quicker and easier to edit than my massive IF function, and now I can have as many foods in there as I want! Thanks a ton! – amota Apr 21 '17 at 1:14