2

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.

Screenshot of Excel worksheet - large nested IF formula

Screenshot 2

  • 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
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.