Vlookup and if statement combined

D

dallasstars10

New Member
Joined
Nov 23, 2009
Messages
18
Okay I have a set of data for a project and I can't figure out how to complete this formula.

I need a formula that will check the current inventory (first the amoutn of baking soda) with the reorder point and IF the inventory has fallen equal to or below the Reorder point, it lists "Reorder, and if not it puts "No"

So I want to check the amount of baking soda (currently 1) (listed in second set of data) and if it is equal to or below (3) that it returns "Reorder" while for the rest it may return "reorder or no" depending on the amount in stock and the reorder point

Then i also must put in another cell "IF it says reorder put the ordering quanity" and "if the reorder is no then put 0 in for the ordering Quantity.

<TABLE style="WIDTH: 564pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=752><COLGROUP><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6001" width=169><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4693" width=132><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3555" width=100><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4181" width=118><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3441" width=97><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4835" width=136><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 127pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=169>Vendor</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 99pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=132>Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=100>Reorder Point</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 88pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=118>Reorder Qty</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 73pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=97>Price per item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 102pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=136>Unit Information</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>ABC corp</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>chocolate chips</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> $ 2.45 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>bags (2 lbs)</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>ABC corp</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>eggs</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>60</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> $ 0.03 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>ABC corp</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>flour</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> $ 1.98 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>bags (5 lbs)</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Jenkins Dairy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>milk</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> $ 2.79 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>gallons</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Poppers Kitchen Supply</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>baking soda</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> $ 1.25 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>container (14 oz)</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Poppers Kitchen Supply</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>salt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> $ 0.68 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>container (2lb)</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>Poppers Kitchen Supply</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>baking powder</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> $ 0.88 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>container (10 oz)</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 226pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=301><COLGROUP><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6001" width=169><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4693" width=132><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 127pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 width=169></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=132>Inventory Check</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>baking powder</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>baking soda</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>chocolate chips</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>8</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>eggs</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>25</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>flour</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>milk</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>salt</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD></TR></TBODY></TABLE>


Thank you!
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 138px"><COL style="WIDTH: 95px"><COL style="WIDTH: 84px"><COL style="WIDTH: 75px"><COL style="WIDTH: 85px"><COL style="WIDTH: 101px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">Vendor</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">Item</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">Reorder Point</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">Reorder Qty</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">Price per item</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">Unit Information</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">ABC corp</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">chocolate chips</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">10</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">25</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">$2.45 </TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">bags (2 lbs)</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">ABC corp</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">eggs</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">30</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">60</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">$0.03 </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc"> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">ABC corp</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">flour</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">8</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">25</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">$1.98 </TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">bags (5 lbs)</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">Jenkins Dairy</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">milk</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">4</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">$2.79 </TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">gallons</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">Poppers Kitchen Supply</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">baking soda</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">4</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">$1.25 </TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">container (14 oz)</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">Poppers Kitchen Supply</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">salt</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">1</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">3</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">$0.68 </TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">container (2lb)</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">Poppers Kitchen Supply</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">baking powder</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">3</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">4</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">$0.88 </TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #ffffcc">container (10 oz)</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt">Inventory Check</TD><TD>Order Status</TD><TD>Qty</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt">baking powder</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD>REORDER</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 10pt">baking soda</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">1</TD><TD>REORDER</TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 10pt">chocolate chips</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">8</TD><TD>REORDER</TD><TD style="TEXT-ALIGN: right">25</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 10pt">eggs</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">25</TD><TD>REORDER</TD><TD style="TEXT-ALIGN: right">60</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 10pt">flour</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">4</TD><TD>REORDER</TD><TD style="TEXT-ALIGN: right">25</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 10pt">milk</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">3</TD><TD>NO</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 10pt">salt</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">1</TD><TD>REORDER</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C13</TD><TD>=IF(ISNA(VLOOKUP(A13,$B$2:$C$8,2,FALSE)),"NOT FOUND",IF(B13<=VLOOKUP(A13,$B$2:$C$8,2,FALSE),"REORDER","NO"))</TD></TR><TR><TD>D13</TD><TD>=IF(C13="NOT FOUND","NOT FOUND",IF(C13="REORDER",VLOOKUP(A13,$B$2:$D$8,3,FALSE),0))</TD></TR><TR><TD>C14</TD><TD>=IF(ISNA(VLOOKUP(A14,$B$2:$C$8,2,FALSE)),"NOT FOUND",IF(B14<=VLOOKUP(A14,$B$2:$C$8,2,FALSE),"REORDER","NO"))</TD></TR><TR><TD>D14</TD><TD>=IF(C14="NOT FOUND","NOT FOUND",IF(C14="REORDER",VLOOKUP(A14,$B$2:$D$8,3,FALSE),0))</TD></TR><TR><TD>C15</TD><TD>=IF(ISNA(VLOOKUP(A15,$B$2:$C$8,2,FALSE)),"NOT FOUND",IF(B15<=VLOOKUP(A15,$B$2:$C$8,2,FALSE),"REORDER","NO"))</TD></TR><TR><TD>D15</TD><TD>=IF(C15="NOT FOUND","NOT FOUND",IF(C15="REORDER",VLOOKUP(A15,$B$2:$D$8,3,FALSE),0))</TD></TR><TR><TD>C16</TD><TD>=IF(ISNA(VLOOKUP(A16,$B$2:$C$8,2,FALSE)),"NOT FOUND",IF(B16<=VLOOKUP(A16,$B$2:$C$8,2,FALSE),"REORDER","NO"))</TD></TR><TR><TD>D16</TD><TD>=IF(C16="NOT FOUND","NOT FOUND",IF(C16="REORDER",VLOOKUP(A16,$B$2:$D$8,3,FALSE),0))</TD></TR><TR><TD>C17</TD><TD>=IF(ISNA(VLOOKUP(A17,$B$2:$C$8,2,FALSE)),"NOT FOUND",IF(B17<=VLOOKUP(A17,$B$2:$C$8,2,FALSE),"REORDER","NO"))</TD></TR><TR><TD>D17</TD><TD>=IF(C17="NOT FOUND","NOT FOUND",IF(C17="REORDER",VLOOKUP(A17,$B$2:$D$8,3,FALSE),0))</TD></TR><TR><TD>C18</TD><TD>=IF(ISNA(VLOOKUP(A18,$B$2:$C$8,2,FALSE)),"NOT FOUND",IF(B18<=VLOOKUP(A18,$B$2:$C$8,2,FALSE),"REORDER","NO"))</TD></TR><TR><TD>D18</TD><TD>=IF(C18="NOT FOUND","NOT FOUND",IF(C18="REORDER",VLOOKUP(A18,$B$2:$D$8,3,FALSE),0))</TD></TR><TR><TD>C19</TD><TD>=IF(ISNA(VLOOKUP(A19,$B$2:$C$8,2,FALSE)),"NOT FOUND",IF(B19<=VLOOKUP(A19,$B$2:$C$8,2,FALSE),"REORDER","NO"))</TD></TR><TR><TD>D19</TD><TD>=IF(C19="NOT FOUND","NOT FOUND",IF(C19="REORDER",VLOOKUP(A19,$B$2:$D$8,3,FALSE),0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
Thank you very very much. This is for a basic computer class in college and I have just one more question!

Now I must

Check the total amount which ends up being <TABLE style="WIDTH: 73pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=97><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3441" width=97><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5be97; WIDTH: 73pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2131287 class=xl65 height=19 width=97 align=right>123.11</TD></TR></TBODY></TABLE>

and if the amount is within 10% of the target budget, put "In line with Budget". If the amount is below that range, put "Below Target Budget" and if it is above the target range, put "exceeds target budget" And this listing should change based upon the value in the target budget cell.

Currently the target budget is
<TABLE style="WIDTH: 226pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=301><COLGROUP><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6001" width=169><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4693" width=132><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 127pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2131287 class=xl65 height=19 width=169>Target Budget</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 99pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=132> $ 60.00 </TD></TR></TBODY></TABLE>

note: When we say within 10% of target budget we mean take 10% of the taget in both directions from the target. So if it is 100 then 10% is 10, so the range is 90-110 inclusive.


Thanks! I appreciate everything. Our teacher is not teaching all of this material and expects us to know how to do most of this
 
Upvote 0
I am sorry to hear your teacher is not teaching the material, however, I don't like to get involved with school homework. If you need additional help I would recommend going to the teacher. Or read the hlep section on IF statements in microsoft Excel.

To give you a nudge in the right direction you want to use an AND statement with your IF statement so IF(AND(...
 
Upvote 0
Can you tell me exactly how to do the "10% within the target budget section"

the project is due tommarow and our teacher is out of her office.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
313
VBAEXCELNew
V
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
817
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
583
epactheactor
E
E
  • Question
Adding a custom message.
Replies
1
Views
422
Micron
M

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top