Excel 2007 and VLookup

[Alain R.]

Hi,

on my sheet 1 called "Apports" i have a table of nutrition with all kind
of foods and their relative kj or Calorie values.
something like:

A B C D
Category Description kj calories
Vegetables Tomatoes 50 8
fruits Orange 62 9.2
fruits kiwi 75 10.1
....
where C and D are data form 100g or 100 ml and so on...

on my sheet 2 called "Alimentation" i have a table with what to eat each
day. On this sheet, i have the following thing:

A B C D
Description qty kj calories
kiwi 70 myformula1 myformula2

myformula1 should display as result: (75/100)*70 = 52.5
and
myformula2 should display as result: (10.1/100)*70 = 7.07

therefore a simple VLOOKUP should be enough
to display correct data, myformula1 should be the
=(VLOOKUP(A2,Apports!$A$2:$D$3,Apports!C:C,false)/100)*B2 to return 52.5
but it doesn't work.

it returns me #REF :-(

thanks for help.

A.

what did i do wrong ?

 

[Pete_UK]

The third parameter of VLOOKUP should be the column number that you
want to get the data from. Try it this way:

=(VLOOKUP(A2,Apports!$A$2:$D$3,3,false)/100)*B2

if you want to get data from column C and like this:

=(VLOOKUP(A2,Apports!$A$2:$D$3,4,false)/100)*B2

if you want data from column D.

Hope this helps.

Pete

 

[GJ_]

Pete_UK schreef:

The third parameter of VLOOKUP should be the column number that you
want to get the data from. Try it this way:

=(VLOOKUP(A2,Apports!$A$2:$D$3,3,false)/100)*B2

if you want to get data from column C and like this:

=(VLOOKUP(A2,Apports!$A$2:$D$3,4,false)/100)*B2

if you want data from column D.

Hope this helps.

Pete

Just curious.

Your lookup range (Apports!$A$2:$D$3) starts at Column A (values: fruit,
fruit, etc), your are searching for "kiwi" (which is in column B), the
value you would like to find is in column C.
Why wouldn't you try something like
=(VLOOKUP(A2,Apports!$B$2:$D$6,2,false)/100)*B2
for myformula1, and
=(VLOOKUP(A2,Apports!$B$2:$D$6,3,false)/100)*B2
for myformula2?

Hope this helps.
GJ

 

[Pete_UK]

Yes, you're right. I just copied his formula into my post and
corrected the third parameter.

Mind you, his formula doesn't quite tie in with his data, as row 2
relates to tomatoes, but that is quite common on posts here.

Pete