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

[Tim G.]

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

 

[yshridhar]

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

 

[T. Valko]

*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)

 

[Tim G.]

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

*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)

 

[T. Valko]

You're welcome. Thanks for the feedback!