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

T

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
 
Y

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

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

Tim G.

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

T. Valko said:
*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)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to use if formula in list (data validation list) 1
to replace cell 3
Change formulas in a column based on a selection. 3
resettable, over-ridable, default cell values 0
Repeat Cell Based on the Value-Excel 2003 3
function to populate a cell where formula is NOT located 6
Return Results of a cell based on cell referenced in a cell 4
Format a blank cell based on the value of another cell 2

Top