How to make this data easy to analyse?

stephanie24

New Member
Joined
Apr 26, 2010
Messages
4
Hi,

I have a problem trying to analyse a big inventory data from 2 different spreadsheets.

Here's what my problem is:

Data 1 (for On hand stock)

<TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=377><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=75> Location ID </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 82pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=109> Item</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Cost</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=65>Available</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>On order</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">TV</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>200</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Radio</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>40</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Vacuum Cleaner</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>34</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Refrigerator</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>380</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>54</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Blender</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>36</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Microwawe</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>190</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">TV</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>200</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Radio</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>40</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Vacuum Cleaner</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>34</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Refrigerator</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>380</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>54</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Blender</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>36</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Microwawe</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>190</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">TV</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>200</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Radio</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>40</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Vacuum Cleaner</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>34</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Refrigerator</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>380</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>54</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Blender</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>36</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Microwawe</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>190</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63> </TD></TR></TBODY></TABLE>

Data for items on order

<TABLE style="WIDTH: 186pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=248><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=75>Location ID</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 82pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=109>Item</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>On Order</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">TV</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>21</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Microwave</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Radio</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Blender</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Vacuum Cleaner</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>56</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">TV</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>67</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Blender</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>68</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Microwave</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>54</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">TV</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>32</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Vacuum Cleaner</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: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Microwave</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>23</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>46</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Microwave</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>49</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>46</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">TV</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>46</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Blender</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>22</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>46</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Radio</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>15</TD></TR></TBODY></TABLE>

What I want is some function which will give me the data on order in the highlighted section in Data 1. I think vlookup won't work because it has multiple entries with the same value. Also, if there is nothing on order for a particular item at a particular location, it should return 0.
I appreciate any help with regards to my question.

Thanks in advance.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

sulakvea

Well-known Member
Joined
Jul 2, 2008
Messages
994
as an example, where would 46 Microwave go? there is no location ID 46 in the first set..
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
One quick and easy way of doing it that would help you understand the workings behind it is to create unique ID like in Column A & G in the sample below and apply a VLOOKUP to it.

There are many other ways, this is a quick way...

Formulas in A2, F2, G2 are copied down

Is this what your after?

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><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><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; COLOR: #0000ff">Unique</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Location ID </TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Item</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Cost</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Available</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00">On order</TD><TD style="FONT-WEIGHT: bold; COLOR: #0000ff">Unique</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Location ID</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Item</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">On Order</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>2TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">200</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD>3TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">21</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>2Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">40</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">12</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2</TD><TD>3Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Calibri">Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>2Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">45</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">34</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">56</TD><TD>2Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>2Refrigerator</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Refrigerator</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">380</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">54</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD>2Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>2Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">25</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">36</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">6</TD><TD>2Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">56</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>2Microwawe</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Microwawe</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">190</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">11</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD>21TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">67</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>23TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">200</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">32</TD><TD>21Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">68</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>23Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">40</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">12</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD>23Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">54</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>23Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">45</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">34</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD>23TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>23Refrigerator</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Refrigerator</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">380</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">54</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD>45Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">45</TD><TD style="FONT-FAMILY: Calibri">Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>23Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">25</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">36</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD>45Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">45</TD><TD style="FONT-FAMILY: Calibri">Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>23Microwawe</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Microwawe</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">190</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">11</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD>46Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">46</TD><TD style="FONT-FAMILY: Calibri">Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">49</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>21TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">200</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">67</TD><TD>46TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">46</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>21Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">40</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">12</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD>46Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">46</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">22</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>21Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">45</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">34</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD>46Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">46</TD><TD style="FONT-FAMILY: Calibri">Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>21Refrigerator</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Refrigerator</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">380</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">54</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>21Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">25</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">36</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">68</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>21Microwawe</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Microwawe</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">190</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">11</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">Not Found</TD><TD> </TD><TD> </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>A2</TD><TD>=B2&C2</TD></TR><TR><TD>F2</TD><TD>=IF(ISNA(VLOOKUP(A2,$G$2:$J$16,4,FALSE)),"Not Found",VLOOKUP(A2,$G$2:$J$16,4,FALSE))</TD></TR><TR><TD>G2</TD><TD>=H2&I2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 

stephanie24

New Member
Joined
Apr 26, 2010
Messages
4
Thanks a lot. I think this should work as of now. But just out of curiosity,is there any other way this issue can be resolved??
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
Thanks a lot. I think this should work as of now. But just out of curiosity,is there any other way this issue can be resolved??
Another way of doing it is in an array

Formula in E2 copied down

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><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><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Location ID </TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Item</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Cost</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Available</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00">On order</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Location ID</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">Item</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Calibri">On Order</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">200</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">21</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">40</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">12</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Calibri">Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">45</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">34</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">56</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Refrigerator</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">380</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">54</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">25</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">36</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">6</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">56</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri">Microwawe</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">190</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">11</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">67</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">200</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">32</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">68</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">40</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">12</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">54</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">45</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">34</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Refrigerator</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">380</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">54</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">45</TD><TD style="FONT-FAMILY: Calibri">Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">25</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">36</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">45</TD><TD style="FONT-FAMILY: Calibri">Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">23</TD><TD style="FONT-FAMILY: Calibri">Microwawe</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">190</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">11</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">46</TD><TD style="FONT-FAMILY: Calibri">Microwave</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">49</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">200</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">67</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">46</TD><TD style="FONT-FAMILY: Calibri">TV</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">40</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">12</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">46</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">22</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Vacuum Cleaner</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">45</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">34</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">46</TD><TD style="FONT-FAMILY: Calibri">Radio</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Refrigerator</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">380</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">54</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Blender</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">25</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">36</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">68</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">21</TD><TD style="FONT-FAMILY: Calibri">Microwawe</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">190</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">11</TD><TD style="FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00">#N/A</TD><TD> </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>E2</TD><TD>{=INDEX($H$2:$H$16,MATCH(1,(A2=$F$2:$F$16)*(B2=$G$2:$G$16),0))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>
 

Watch MrExcel Video

Forum statistics

Threads
1,095,782
Messages
5,446,469
Members
405,403
Latest member
horace james

This Week's Hot Topics

Top