Multiple Result w/ one Criteria

[ruckuz]

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl114 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20 x:str="'090728">090728</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right width=64 x:num>100</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl114 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:str="'090728">090728</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>200</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl114 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:str="'090728">090728</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>300</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl114 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num>99702</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>400</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl114 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num>99702</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>500</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl114 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num>99702</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>600</TD></TR></TBODY></TABLE>

Above is an example.
I want a formula to look up say 99702 and return all the results that corresponds to 99702, such as 400, 500, 600.

I've google it and saw index small function will work but i tried it and it doesnt. Please help.

 

[njimack]

Ignore this post - misread the question

 

[ruckuz]

Ignore this post - misread the question

ignore my post?

 

[njimack]

Assuming your data starts from A2, enter the following in any cell (ctrl+shift+enter), then copy down as far as required

=INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=99702,ROW($A$2:$A$7)),ROW($A1)))

 

[ruckuz]

Assuming your data starts from A2, enter the following in any cell (ctrl+shift+enter), then copy down as far as required

=INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=99702,ROW($A$2:$A$7)),ROW($A1)))

<TABLE style="WIDTH: 558pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=744 border=0 x:str><COLGROUP><COL style="WIDTH: 462pt; mso-width-source: userset; mso-width-alt: 11264" width=616><COL style="WIDTH: 96pt" width=128><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl115 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 462pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=616 height=20 x:num u1:str="'090728">90728


</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 96pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=128 x:num u1:num>100</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl115 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num u1:str="'090728">90728</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num u1:num>200</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl115 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num u1:str="'090728">90728</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num u1:num>300</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl115 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num u1:num>99702</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num u1:num>400</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl115 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num u1:num>99702</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num u1:num>500</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl115 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num u1:num>99702</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num u1:num>600</TD></TR>


<TR style="HEIGHT: 15pt" height=20><TD class=xl115 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl116 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:err="#VALUE!">=INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=99702,ROW($A$2:$A$7)),ROW($A1)))


</TD><TD id=td_post_2580398 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR>
</TBODY></TABLE>
below is what i entered and i get a value error

i started my data in a2

 

[T. Valko]

Assuming your data starts from A2, enter the following in any cell (ctrl+shift+enter), then copy down as far as required

=INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=99702,ROW($A$2:$A$7)),ROW($A1)))

Using that specific syntax you're going to miss the first results.

Try it like this...

Data in the range A2:B7

D2 = the code to lookup like 99702

Enter this formula in E2. This will return the count of records for the lookup criteria.

=COUNTIF(A2:A7,D2)

Enter this array formula** in F2 and copy down until you get blanks:

=IF(ROWS(F$2:F2) > E$2,"",INDEX(B:B,SMALL(IF(A$2:A$7=D$2,ROW(A$2:A$7)),ROWS(F$2:F2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[njimack]

Did you enter using ctrl+shift+enter?
Slight tweak to the formula: =INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=99702,ROW($A$2:$A$7)-1),ROW($A1)))

 

[ruckuz]

Did you enter using ctrl+shift+enter?
Slight tweak to the formula: =INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=99702,ROW($A$2:$A$7)-1),ROW($A1)))

Done. that worked. Thanks.