Need help to resolve #N/A error from VLOOKUP

[jonanth]

Hi all,

I am new to excel but am trying to complete a uni assignment.

I am making a food consumption log where I have columns labelled "Food", "Servings", "Calories", "Fat", "Protein", "Carbs" and "Fibre."

The "Food" column consists of a drop down box of thousands of food options from a "Food Nutrition Data" sheet that lists the food and its corresponding calories, fat, protein, carbs and fibre content horizontally for each food. My task is to input a formula into the "food consumption log" sheet that will retrieve the Calories, Fat, Protein, Carbs, and Fibre from "Food Nutrition Data" sheet and multiply it by the servings in the second column for whatever food the user selects from the "Food" drop down box.

The formula I am using is =IF($C2="","",$D2*VLOOKUP($C2,FoodLookup,MATCH(E$1,'Food Nutrition Data'!$C$1:$H$1,0),0)) but it keeps saying #N/A. (FoodLookup is just the named range for the data in the Food Nutrition Table)

I hope you can understand what I'm trying to do from this description. Any help is appreciated!

Thank you

Food Nutrition Data: Food Nutrition Data.png
Food Consumption Log: Food Consumption Log.png

[Sam Capricci]

is it possible to upload a sample workbook AND if so, please hand enter in an adjacent cell the expected results.

[jonanth]

Hi Sam, thanks for your reply!

I have attached some sections of the workbooks I am working in and have included the expected results. I am not sure if this is what you meant but I hope it helps

I have also attached a workbook I found online that I have been trying to copy as it does a similar thing to what I'm trying to do.

Thanks!

[jonanth]

Replied below!

[Sam Capricci]

I would use this simple vlookup in E2 and dragged down and right from there.
It keys on the match between the food description in col C of the consumption log and the match to the food nutrition data tab col C.
if the match isn't exact you'll get a #N/A error (spaces in one that aren't in the other for example).

[AliGW]

In E2 copied across and down:

=$D2*INDEX('Food Nutrition Data'!D$2:D$6,MATCH($C2,'Food Nutrition Data'!$C$2:$C$6,0))

[jasond1992]

Formula Above is really works for this thread.

[AliGW]

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

On this occasion, you just seem to have copied the solution I gave in post #6.

[jasond1992]

Wahhhhhhh sorry ma'am I didn't get your formula. I have the same scenario with the thread that's why I try to give it to him. But when I see right now your formula it's literally the same. for that ma'am I will edit my answer for this thread. I promise I didn't not copy and I didn't see the formula before I get his example documents. Sorry AliGW.

[Sam Capricci]

What happened to the formula I pasted?
which I thought was this... =$D2*VLOOKUP($C2,'Food Nutrition Data'!$C2:D6,COLUMN(B$1),FALSE)

[AliGW]

There was no formula in your post when I first looked at the thread, Sam, and your post hasn't been edited by anyone - maybe your copy paste didn't work?

[Sam Capricci]

yes, I could swear I saw the formula there before I logged off which is weird. I noticed that there was no "edited" note under my post.
well, that is what I worked on for what it was worth.

[AliGW]

Well, at least ours are different from each other's!!!