Better alternative to large nested IF formula in Excel?

Question

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.

Answer 1

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( ... ))