Search and multiple return values

[rparry2112]

I am attempting to do a search based on 2 criteria that will return several matches and I can't figure out how to display anything but the first match.

I have 3 colums of data

Column a Column b Column C
Order date Order Charged Order #
9/1/2008 Yes 1234
9/1/2008 Yes 2341
9/1/2008 Yes 4561

I want to do a search based on the date = date that is populated in a cell for reference and the order charged = Yes and then i want to return on another sheet in column a the following order numbers.

1234
2341
4561

Each in a cell down a column.

Anyone have any suggestions on how to do this?

 

[nbrcrunch]

http://www.mrexcel.com/tip062.shtml

 

[sous2817]

Couldn't you just use the autofilter?

 

[Aladin Akyurek]

The data is in A1:C4 on Sheet1.

The processing on Sheet2 in column A as shown below:

<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=71 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=71 height=17>Date</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39692">9/1/2008</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Charged?</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Yes</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>List</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1234</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2341</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>4561</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str=""> </TD></TR></TBODY></TABLE>

A2 houses a date of interest.

A4 houses a flag value for charged.

A6:

=SUMPRODUCT(--(Sheet1!A2:A4=A2),--(Sheet1!B2:B4=A4))

A8:

Control+shift+enter, not just enter...

=IF(ROWS($A$8:A8)<=$A$6,INDEX(Sheet1!$C$2:$C$4,
  SMALL(IF(Sheet1!$A$2:$A$4=$A$2,IF(Sheet1!$B$2:$B$4=$A$4,
    ROW(Sheet1!$C$2:$C$4)-ROW(Sheet1!$C$2)+1)),
      ROWS($A$8:A8))),"")

and copy down.

I am attempting to do a search based on 2 criteria that will return several matches and I can't figure out how to display anything but the first match.

I have 3 colums of data

Column a Column b Column C
Order date Order Charged Order #
9/1/2008 Yes 1234
9/1/2008 Yes 2341
9/1/2008 Yes 4561

I want to do a search based on the date = date that is populated in a cell for reference and the order charged = Yes and then i want to return on another sheet in column a the following order numbers.

1234
2341
4561

Each in a cell down a column.

Anyone have any suggestions on how to do this?