Returning multiple values from a lookup list

[asawhill]

I'm trying to lookup and return mulitple values from a list based one a single criteria. I know that this can easily be done in Access or in Excel via VBA programming or autofilters. Is there a way to do this using a function or combination of functions?

For example, in my data table, I have two columns: one for product category, and one for stock number. I would like to return all the stock numbers for a particular product category based on an input in a separate cell.

Any suggestions? Thanks.

 

[Aladin Akyurek]

I need to use a formula. Thanks.

Creating a distinct or unique list of items using a formula system...

Book4
	A	B	C	D
1				4
2	StockNumber	0		Distinct StockNumber
3	3103	1		3103
4	3103			3546
5	3546	2		4587
6	4587	3		6257
7	4587
8	4587
9	6257	4
10
Sheet1

Formulas...

B2 must house a 0.

B3, copied down:

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99999999999999E+307,$B$2:B2)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,B:B)

D3, copied down:

=IF(ROW()-ROW(D$3)+1<=$D$1,INDEX(A:A,MATCH(ROW()-ROW(D$3)+1,B:B)),"")

 

[asawhill]

Beautiful! Thanks!

 

[saurabh726]

aladin!!

that formula also worked beauty for me u gaved me out here

http://www.mrexcel.com/board2/viewtopic.php?t=94280&highlight=

i just wanna know exactly how does it works as i fail to understand...

appreciate if u help

Saurabh