Need help with VLOOKUP

  • Thread starter Thread starter sfi
  • Start date Start date
S

sfi

Have two worksheets

1st worksheet is called "Food Database"

A B C D E F G
H
Blank Item# Brand Pack Ingredients Pricing Units oz/ea. Cost Per oz/ea

2nd worksheet is called "Recipe Calculation"

A B C D E F G H I
J
Blank Blank Blank Blank Ingredient Blank Blank Qty Unit Unit Cost

Have formula =VLOOKUP(E6,'Food DataBase'!A1:H2500,8,TRUE) in "Recipe
Calculation" J(Unit Cost)

E6 is the first ingredient listed in Recipe Calculation that I want it to
lookup

Want it to lookup Recipe Calculation E(Ingredient) and return the value in
Food DataBase H(Cost per Oz/ea)
to Recipe Calculation J(Unit Cost)

Currently it returns a 0
It should return $ 0.17

What did I do wrong

Thanks
DuWayne
 
The lookup column should be the first column in your range. Also you
probably want to set the last parameter to false so it looks for the exact
ingredient. Try:
=VLOOKUP(E6,'Food DataBase'!E1:H2500,4,FALSE)

-Simon
 
hi Sfi

=VLOOKUP(E6,'Food DataBase'!e1:H2500,4,TRUE)

the range that you were defined A1:h2500 was wrong, voolkup doesn't found
the e6 value on the A column.

hth
regards from Brazil
Marcelo

"sfi" escreveu:
 
Thanks Simon for your previous help
I have a new problem
I have added a ComboBox to lookup the ingredient
in e6. Now I'm getting a new error #N/A.
What do I replace e6 with to work.
The ComboBox is named ComboBox1

=VLOOKUP(E6,'Food DataBase'!E1:H2500,4,FALSE)
=VLOOKUP(ComboBox1,'Food DataBase'!E1:H2500,4,FALSE)

Thanks
 
Sorry I didn't see your new post till today. Did you add the ComboBox from
the Control toolbar or Forms toolbar? Those two are different and I would
need to know which.

-Simon
 
Control Tool Box and Forms Tool Box

SimonCC said:
Sorry I didn't see your new post till today. Did you add the ComboBox from
the Control toolbar or Forms toolbar? Those two are different and I would
need to know which.

-Simon
Click to expand...
 
Actually I would recommend using the Combo Box from the Forms toolbar. After
you have placed the ComboBox in the Recipe Calculation sheet, just right
click on it and go to Format Control. Under the Control tab, Input range
would be 'Food DataBase'!E1:E2500. For Cell link, you can use E6 or whatever
other blank cell you like. If you picked E6 for Cell link, the formula would
be:
=VLOOKUP(INDEX('Food DataBase'!E1:E2500,E6),'Food DataBase'!E1:H2500,4,FALSE)
If you picked another cell for Cell link, just replace the E6 in the formula
with whatever other cell you picked.

-Simon
 
Back
Top