lookup and Returning Multiple lines

[mikearnold]

I hope someone can help me. I have a construction estimate workbook that works as follows:

PO#, vendor#, Item#, Item Description, Price,
1500 7033 2345 2x4x12 3.54

1501 3454 2343 Termite Treat 100.00

The Price is returned by two way Lookup using the vendor# and the Item# and returning the Price, which is working Fine however!

I want to return all the items from the estimate that have the PO# 1500 for example to return to the PO Page 1500, the PO# 1501 would return on the po Page 1501 and so on. I have tried vLookup, but it can not see past the first Line 1500 and cannot see other 1500s and so on. thanks mikearnold

 

[markmzz]

Maybe this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">PO#</td><td style="text-align: center;;"> vendor#</td><td style="text-align: center;;"> Item#</td><td style="text-align: center;;"> Item Description</td><td style="text-align: center;;"> Price</td><td style="text-align: right;;"></td><td style="text-align: center;;">PO#</td><td style="text-align: center;;"> vendor#</td><td style="text-align: center;;"> Item#</td><td style="text-align: center;;"> Item Description</td><td style="text-align: center;;"> Price</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1500</td><td style="text-align: center;;">7033</td><td style="text-align: center;;">2345</td><td style="text-align: center;;">2x4x12</td><td style="text-align: center;;">3.54</td><td style="text-align: right;;"></td><td style="text-align: center;;">1500</td><td style="text-align: center;;">7033</td><td style="text-align: center;;">2345</td><td style="text-align: center;;">2x4x12</td><td style="text-align: center;;">3.54</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1500</td><td style="text-align: center;;">7034</td><td style="text-align: center;;">2346</td><td style="text-align: center;;">2x4x13</td><td style="text-align: center;;">4.54</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">7034</td><td style="text-align: center;;">2346</td><td style="text-align: center;;">2x4x13</td><td style="text-align: center;;">4.54</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">1500</td><td style="text-align: center;;">7035</td><td style="text-align: center;;">2347</td><td style="text-align: center;;">2x4x14</td><td style="text-align: center;;">5.54</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">7035</td><td style="text-align: center;;">2347</td><td style="text-align: center;;">2x4x14</td><td style="text-align: center;;">5.54</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">1500</td><td style="text-align: center;;">7036</td><td style="text-align: center;;">2348</td><td style="text-align: center;;">2x4x15</td><td style="text-align: center;;">6.54</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">7036</td><td style="text-align: center;;">2348</td><td style="text-align: center;;">2x4x15</td><td style="text-align: center;;">6.54</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">1501</td><td style="text-align: center;;">3454</td><td style="text-align: center;;">2343</td><td style="text-align: center;;">Termite Treat1</td><td style="text-align: center;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">1501</td><td style="text-align: center;;">3455</td><td style="text-align: center;;">2344</td><td style="text-align: center;;">Termite Treat2</td><td style="text-align: center;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">1501</td><td style="text-align: center;;">3456</td><td style="text-align: center;;">2345</td><td style="text-align: center;;">Termite Treat3</td><td style="text-align: center;;">300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">1501</td><td style="text-align: center;;">3457</td><td style="text-align: center;;">2346</td><td style="text-align: center;;">Termite Treat4</td><td style="text-align: center;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">1501</td><td style="text-align: center;;">3458</td><td style="text-align: center;;">2347</td><td style="text-align: center;;">Termite Treat5</td><td style="text-align: center;;">500</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">***</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIF(<font color="Red">$A$2:$A$10,$G$2</font>)>=ROWS(<font color="Red">$H$2:$H2</font>),INDEX(<font color="Red">B$2:B$10,SMALL(<font color="Green">(<font color="Purple">$A$2:$A$10=$G$2</font>)*(<font color="Purple">ROW(<font color="Teal">$A$2:$A$10</font>)-ROW(<font color="Teal">$A$1</font>)</font>),COUNTIF(<font color="Purple">$A$2:$A$10,"<>"&$G$2</font>)+ROWS(<font color="Purple">$H$2:$H2</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[mikearnold]

thank you i will try this mike