Return a list of cells based on data in a single cell.
 

Basically, I want to enter a model number in say, cell A1 and if the value of
that cell is the same as the model number in any cell A2:A80, I want any of
the corresponding serial numbers which are located in B2:B80 to be returned
as a list in another area such as column C. I'm sure that this is going to
call for an array and lookup, but I cannot get the formula that I found in
another area of this site to work. Thanks for your help.

Here is a sample of what I would like....

A B
1 modelx
2 modelw 8495065
3 modelx 7343034
4 modelx 9485895
5 modelu 8589439
6 modelx 9869949

And I would like to have these values returned


c
7343034
9485895
9869949

Return a list of cells based on data in a single cell.
 

Pivot table is the best option.
Try the following formula.
=IF(ROWS($1:1)<=COUNTIF($A$2:$A$80,$A$1),INDEX($B$ 2:$B$80,SMALL(IF($A$2:$A$80=$A$1,ROW($A$2:$A$80)-MIN(ROW($A$2:$A$80))+1),ROWS($1:1))),"")
Paste the formula in C2. A1= model number. It is an array formula. Enter
it with Ctrl+shift+enter. No just enter. Copy down the formula.
Change the ranges according to your data.
Best wishes
Sreedhar

"Tim G." wrote:

Basically, I want to enter a model number in say, cell A1 and if the value of
that cell is the same as the model number in any cell A2:A80, I want any of
the corresponding serial numbers which are located in B2:B80 to be returned
as a list in another area such as column C. I'm sure that this is going to
call for an array and lookup, but I cannot get the formula that I found in
another area of this site to work. Thanks for your help.

Here is a sample of what I would like....

A B
1 modelx
2 modelw 8495065
3 modelx 7343034
4 modelx 9485895
5 modelu 8589439
6 modelx 9869949

And I would like to have these values returned


c
7343034
9485895
9869949

Return a list of cells based on data in a single cell.
 

*IF* the serial numbers are NUMERIC numbers and none have leading 0s this
array formula** saves a few keystrokes:

=IF(ROWS(C$1:C1)<=COUNTIF(A$2:A$6,A$1),SMALL(IF(A$ 2:A$6=A$1,B$2:B$6),ROWS(C$1:C1)),"")

Copy down until you get blanks.

It will extract the serial numbers sorted in ascending order.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"yshridhar" wrote in message
...
Pivot table is the best option.
Try the following formula.
=IF(ROWS($1:1)<=COUNTIF($A$2:$A$80,$A$1),INDEX($B$ 2:$B$80,SMALL(IF($A$2:$A$80=$A$1,ROW($A$2:$A$80)-MIN(ROW($A$2:$A$80))+1),ROWS($1:1))),"")
Paste the formula in C2. A1= model number. It is an array formula.
Enter
it with Ctrl+shift+enter. No just enter. Copy down the formula.
Change the ranges according to your data.
Best wishes
Sreedhar

"Tim G." wrote:

Basically, I want to enter a model number in say, cell A1 and if the
value of
that cell is the same as the model number in any cell A2:A80, I want any
of
the corresponding serial numbers which are located in B2:B80 to be
returned
as a list in another area such as column C. I'm sure that this is going
to
call for an array and lookup, but I cannot get the formula that I found
in
another area of this site to work. Thanks for your help.

Here is a sample of what I would like....

A B
1 modelx
2 modelw 8495065
3 modelx 7343034
4 modelx 9485895
5 modelu 8589439
6 modelx 9869949

And I would like to have these values returned


c
7343034
9485895
9869949

Return a list of cells based on data in a single cell.
 

It worked EXACTLY like I wanted. Thanks y'all.

"T. Valko" wrote:

*IF* the serial numbers are NUMERIC numbers and none have leading 0s this
array formula** saves a few keystrokes:

=IF(ROWS(C$1:C1)<=COUNTIF(A$2:A$6,A$1),SMALL(IF(A$ 2:A$6=A$1,B$2:B$6),ROWS(C$1:C1)),"")

Copy down until you get blanks.

It will extract the serial numbers sorted in ascending order.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"yshridhar" wrote in message
...
Pivot table is the best option.
Try the following formula.
=IF(ROWS($1:1)<=COUNTIF($A$2:$A$80,$A$1),INDEX($B$ 2:$B$80,SMALL(IF($A$2:$A$80=$A$1,ROW($A$2:$A$80)-MIN(ROW($A$2:$A$80))+1),ROWS($1:1))),"")
Paste the formula in C2. A1= model number. It is an array formula.
Enter
it with Ctrl+shift+enter. No just enter. Copy down the formula.
Change the ranges according to your data.
Best wishes
Sreedhar

"Tim G." wrote:

Basically, I want to enter a model number in say, cell A1 and if the
value of
that cell is the same as the model number in any cell A2:A80, I want any
of
the corresponding serial numbers which are located in B2:B80 to be
returned
as a list in another area such as column C. I'm sure that this is going
to
call for an array and lookup, but I cannot get the formula that I found
in
another area of this site to work. Thanks for your help.

Here is a sample of what I would like....

A B
1 modelx
2 modelw 8495065
3 modelx 7343034
4 modelx 9485895
5 modelu 8589439
6 modelx 9869949

And I would like to have these values returned


c
7343034
9485895
9869949

Return a list of cells based on data in a single cell.
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Tim G." wrote in message
...
It worked EXACTLY like I wanted. Thanks y'all.

"T. Valko" wrote:

*IF* the serial numbers are NUMERIC numbers and none have leading 0s this
array formula** saves a few keystrokes:

=IF(ROWS(C$1:C1)<=COUNTIF(A$2:A$6,A$1),SMALL(IF(A$ 2:A$6=A$1,B$2:B$6),ROWS(C$1:C1)),"")

Copy down until you get blanks.

It will extract the serial numbers sorted in ascending order.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"yshridhar" wrote in message
...
Pivot table is the best option.
Try the following formula.
=IF(ROWS($1:1)<=COUNTIF($A$2:$A$80,$A$1),INDEX($B$ 2:$B$80,SMALL(IF($A$2:$A$80=$A$1,ROW($A$2:$A$80)-MIN(ROW($A$2:$A$80))+1),ROWS($1:1))),"")
Paste the formula in C2. A1= model number. It is an array formula.
Enter
it with Ctrl+shift+enter. No just enter. Copy down the formula.
Change the ranges according to your data.
Best wishes
Sreedhar

"Tim G." wrote:

Basically, I want to enter a model number in say, cell A1 and if the
value of
that cell is the same as the model number in any cell A2:A80, I want
any
of
the corresponding serial numbers which are located in B2:B80 to be
returned
as a list in another area such as column C. I'm sure that this is
going
to
call for an array and lookup, but I cannot get the formula that I
found
in
another area of this site to work. Thanks for your help.

Here is a sample of what I would like....

A B
1 modelx
2 modelw 8495065
3 modelx 7343034
4 modelx 9485895
5 modelu 8589439
6 modelx 9869949

And I would like to have these values returned


c
7343034
9485895
9869949