|
Need help with VLOOKUP
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 |
Need help with VLOOKUP
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 "sfi" wrote: 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 |
Need help with VLOOKUP
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: 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 |
Need help with VLOOKUP
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 "sfi" wrote in message ... 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 |
Need help with VLOOKUP
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 "sfi" wrote: 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 "sfi" wrote in message ... 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 |
Need help with VLOOKUP
Control Tool Box and Forms Tool Box
"SimonCC" wrote in message ... 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 "sfi" wrote: 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 "sfi" wrote in message ... 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 |
Need help with VLOOKUP
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 "sfi" wrote: Control Tool Box and Forms Tool Box "SimonCC" wrote in message ... 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 "sfi" wrote: 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 "sfi" wrote in message ... 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 |
| All times are GMT +1. The time now is 04:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com