making a formula for a drop down list

New Contributor

Hello everyone

I am trying to make a calculator giving nutrition information from different baby milk formulas. The information for this is in a table in a separate excel sheet, Sheet 2 The milk formulas can be chosen from a drop down list in the main Calculator sheet. How do I create a formula which will take the right values for the type of milk chosen from the table in Sheet 2

4 Replies


You can use VLOOKUP for this, for example in C2:


This formula can be filled down. See the attached version.



I named the range in Sheet 2 "Data"

I added a sample record in Row # 3 using your drop list

I created the function in cell D 3 as follows:


Copy the function to the left and down

Test using different items from the drop list

You may consider wrapping it in an IFERROR function


Hope that helps

File attached


@Hans Vogelaar 

Thank you. That was really quick. I can see that you have chosen the range in Sheet 2!$A$3:$F$9. However in the drop down list, I cannot see the last 2 names of the milk formulas, Nutriprem 1and Nutriprem 2. Any thoughts?


In the workbook that you attached to the first post, the source of the data validation dropdown was set to Sheet 2!$A$3:$F$7. I hadn't changed that.