Searching more than one identical records in one array

[khawarameer]

Hello all,

I am trying to search for record in a list which is given below.
Sheet1

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 65px;"><col style="width: 135px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Bill # </td><td>Discription</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>ABC-2434</td><td>Cotton</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>ABC-3333</td><td>Olive Oil</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>ABC-4560</td><td>Palm Oil</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>ABC-2434</td><td>Cotton</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>ABC-4444</td><td>sugar</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>ABC-1230</td><td>100% Cotton</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>ABC-2434</td><td>Cotton</td></tr></tbody></table>


I am trying to search Bill # using this formula.
=VLOOKUP($E$1,A2:B8,1,0)
This formula works best when there is only one value in array. However when there are more than one values it shows only the first match.

What should i do here. Your help required.
Regards

 

[RichKat]

If you want to display only a certain Bill# reguardless of what is in column B then I would use filtering instead of vlookup. Click the data tab and then filter. Then you can click the drop down and choose the bill number you want to filter on and it will display only the ones you check. Sorry if this is too simplistic.

 

[Matty]

Hello,

So, given the data set provided, for ABC-2434, you'd want "Cotton" returning three times?

Is this right? Or can there be different Descriptions against the same Bill #?

Matty

 

[VoG]

Perhaps

<b>Sheet10</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:82px;" /><col style="width:94px;" /><col style="width:31px;" /><col style="width:31px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">F</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td >Bill # </td><td >Discription</td><td > </td><td > </td><td >ABC-2434</td><td style="color:#0000ff; "> </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td >ABC-2434</td><td >Cotton</td><td > </td><td > </td><td style="text-align:right; ">3</td><td >Cotton</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td >ABC-3333</td><td >Olive Oil</td><td > </td><td > </td><td > </td><td >Cotton</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td >ABC-4560</td><td >Palm Oil</td><td > </td><td > </td><td > </td><td >Cotton</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td >ABC-2434</td><td >Cotton</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td >ABC-4444</td><td >sugar</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td >ABC-1230</td><td >100% Cotton</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td >ABC-2434</td><td >Cotton</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=COUNTIF(A2:A10,E1)</td></tr><tr><td >F2</td><td >{=IF(ROWS<span style=' color:008000; '>(F$2:F2)</span><=$E$2,INDEX<span style=' color:008000; '>($A$2:$B$10,SMALL<span style=' color:#0000ff; '>(IF<span style=' color:#ff0000; '>($A$2:$A$10=$E$1,ROW<span style=' color:#804000; '>($A$2:$A$10)</span>-ROW<span style=' color:#804000; '>($A$2)</span>+1)</span>,ROWS<span style=' color:#ff0000; '>(F$2:F2)</span>)</span>,2)</span>,"")}</td></tr><tr><td >F3</td><td >{=IF(ROWS<span style=' color:008000; '>(F$2:F3)</span><=$E$2,INDEX<span style=' color:008000; '>($A$2:$B$10,SMALL<span style=' color:#0000ff; '>(IF<span style=' color:#ff0000; '>($A$2:$A$10=$E$1,ROW<span style=' color:#804000; '>($A$2:$A$10)</span>-ROW<span style=' color:#804000; '>($A$2)</span>+1)</span>,ROWS<span style=' color:#ff0000; '>(F$2:F3)</span>)</span>,2)</span>,"")}</td></tr><tr><td >F4</td><td >{=IF(ROWS<span style=' color:008000; '>(F$2:F4)</span><=$E$2,INDEX<span style=' color:008000; '>($A$2:$B$10,SMALL<span style=' color:#0000ff; '>(IF<span style=' color:#ff0000; '>($A$2:$A$10=$E$1,ROW<span style=' color:#804000; '>($A$2:$A$10)</span>-ROW<span style=' color:#804000; '>($A$2)</span>+1)</span>,ROWS<span style=' color:#ff0000; '>(F$2:F4)</span>)</span>,2)</span>,"")}</td></tr><tr><td >F5</td><td >{=IF(ROWS<span style=' color:008000; '>(F$2:F5)</span><=$E$2,INDEX<span style=' color:008000; '>($A$2:$B$10,SMALL<span style=' color:#0000ff; '>(IF<span style=' color:#ff0000; '>($A$2:$A$10=$E$1,ROW<span style=' color:#804000; '>($A$2:$A$10)</span>-ROW<span style=' color:#804000; '>($A$2)</span>+1)</span>,ROWS<span style=' color:#ff0000; '>(F$2:F5)</span>)</span>,2)</span>,"")}</td></tr><tr><td >F6</td><td >=IF(ROWS<span style=' color:008000; '>(F$2:F6)</span><=$E$2,INDEX<span style=' color:008000; '>($A$2:$B$10,SMALL<span style=' color:#0000ff; '>(IF<span style=' color:#ff0000; '>($A$2:$A$10=$D$2,ROW<span style=' color:#804000; '>($A$2:$A$10)</span>-ROW<span style=' color:#804000; '>($A$2)</span>+1)</span>,ROWS<span style=' color:#ff0000; '>(F$2:F6)</span>)</span>,2)</span>,"")</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


Adapted from http://www.ozgrid.com/forum/showthread.php?t=72463

 

[khawarameer]

Thanks Peter.

Just one more thing, the example i put in my message was dummy data. The actual record comprise of many rows. Can there be a formula to automatically determine last row. so your solution may work where ever i want.

Regards

 

[VoG]

I think that you just need to expand the range in the formula in E2 and column F to, say, A2:A1000. You can then drag the formula in column F as far down as you need.

 

[khawarameer]

I think that you just need to expand the range in the formula in E2 and column F to, say, A2:A1000. You can then drag the formula in column F as far down as you need.

Hmmm I wonder how much memory it will consume.
But thanks Peter for the help.

Best Regards