Formula help --- lookup?

[lelesgirl]

How would I write a formula for the following:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

<TABLE class=MsoNormalTable style="MARGIN-LEFT: -1.15pt; WIDTH: 507.05pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=676 border=0><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 83pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt" vAlign=bottom width=111 bgColor=silver height=34>

Item Numb<o></o>

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 44pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt" vAlign=bottom width=59 bgColor=silver height=34>

MIN<o></o>

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 44pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 25.5pt" vAlign=bottom width=59 bgColor=silver height=34>

MAX<o></o>

​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; BACKGROUND: silver; PADDING-BOTTOM: 0in; WIDTH: 61.05pt; PADDING-TOP: 0in; HEIGHT: 25.5pt" vAlign=bottom width=81 bgColor=silver height=34>

LOCATION<o></o>

​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; BACKGROUND: silver; PADDING-BOTTOM: 0in; WIDTH: 64pt; PADDING-TOP: 0in; HEIGHT: 25.5pt" vAlign=bottom width=85 bgColor=silver height=34>

QOH<o></o>

​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; BACKGROUND: silver; PADDING-BOTTOM: 0in; WIDTH: 77pt; PADDING-TOP: 0in; HEIGHT: 25.5pt" vAlign=bottom width=103 bgColor=silver height=34>

<o></o>​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; BACKGROUND: silver; PADDING-BOTTOM: 0in; WIDTH: 48pt; PADDING-TOP: 0in; HEIGHT: 25.5pt" vAlign=bottom width=64 bgColor=silver height=34>

​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; BACKGROUND: #ffff99; PADDING-BOTTOM: 0in; WIDTH: 86pt; PADDING-TOP: 0in; HEIGHT: 25.5pt" vAlign=bottom width=115 bgColor=#ffff99 height=34>

<o></o>

​

</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 83pt; PADDING-TOP: 0in; HEIGHT: 16.5pt" vAlign=bottom noWrap width=111 height=22>

11-011266-06<o></o>

​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 44pt; PADDING-TOP: 0in; HEIGHT: 16.5pt" vAlign=bottom noWrap width=59 height=22>

80<o></o>

​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 44pt; PADDING-TOP: 0in; HEIGHT: 16.5pt" vAlign=bottom noWrap width=59 height=22>

480<o></o>

​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 61.05pt; PADDING-TOP: 0in; HEIGHT: 16.5pt" vAlign=bottom noWrap width=81 height=22>

603<o></o>

​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 64pt; PADDING-TOP: 0in; HEIGHT: 16.5pt" vAlign=bottom noWrap width=85 height=22>

<o></o>​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 77pt; PADDING-TOP: 0in; HEIGHT: 16.5pt" vAlign=bottom noWrap width=103 height=22>

​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; WIDTH: 48pt; PADDING-TOP: 0in; HEIGHT: 16.5pt" vAlign=bottom noWrap width=64 height=22>

<o></o>​

</TD><TD style="PADDING-RIGHT: 5.4pt; PADDING-LEFT: 5.4pt; BACKGROUND: #ffff99; PADDING-BOTTOM: 0in; WIDTH: 86pt; PADDING-TOP: 0in; HEIGHT: 16.5pt" vAlign=bottom noWrap width=115 bgColor=#ffff99 height=22></TD></TR></TBODY></TABLE>​

<o></o>
Basically what I want to do is if the item number in column a and the location in column d match the given, then from another spreadsheet, I need it to return a quantity.<o></o>
<o></o>
I have tried v lookup but that doesn’t work when there are 2 columns (or I can’t get it to work).<o></o>

 

[texasalynn]

Welcome to MrExcel board...

so what does the data on the other sheet look like

 

[Andrew Poulsom]

If there can be only 1 occurrence of each combination of item number and location, you can use a formula like:

=SUMPRODUCT(--(Sheet2!A1:A100=A2),--(Sheet2!B2:B100=D2),Sheet2!C1:C100)

Adjust the references to suit, but make sure that all the lookup ranges have the same number of rows and avoid using entire columns.

 

[lelesgirl]

sorry...first timer here...other spreadsheet is as follows:

<TABLE style="WIDTH: 191pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=255 border=0 x:str><COLGROUP><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><TBODY><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 108pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=144 height=22>Brk(Item Nbr)</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 42pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=56>Bin</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 41pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=55>Quantity</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>11-011278-36</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>101</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>48</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>11-011578-25</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>101</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>32</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>11-016278-25</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>101</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>48</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>11-011578-25</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>102</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>36</TD></TR></TBODY></TABLE>

 

[texasalynn]

no problem - but there is no location on the lookup data? So it would not be able to do a match.