Returning multiple values from a lookup list

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Jul 26, 2004

• #2

Book1
	A	B	C	D	E	F	G	H	I
1	Category	StockNo.
2	A	3168		A=	3168,8884,5326,4428
3	A	8884
4	B	2199
5	A	5326
6	C	4141
7	A	4428
8
9
10
Sheet1

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Jul 26, 2004

• #3

In response to a PM...

Book1
	A	B	C	D	E	F	G
1	Category	StockNo.
2	A	3168		8884
3	A	8884		5326
4	B	2199		4428
5	A	5326		3168
6	C	4141
7	A	4428
8
9
Sheet1

 

Upvote 0

S

saurabh726

Well-known Member

Joined

Dec 13, 2003

Messages

891

• Jul 26, 2004

• #4

{=UNIQUEVALUES(IF($A$2:$A$7="A",$B$2:$B$7,""))}

Click to expand...

Mark w dont u think to use this function he has to instal more function other wise the same wont work..???

Saurabh

 

Upvote 0

S

saurabh726

Well-known Member

Joined

Dec 13, 2003

Messages

891

• Jul 26, 2004

• #5

http://www.mrexcel.com/board2/viewtopic.php?t=49044#javascript:void(0);

Here is an alternative for this...

Saurabh

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Jul 26, 2004

• #6

saurabh726 said:

{=UNIQUEVALUES(IF($A$2:$A$7="A",$B$2:$B$7,""))}

Click to expand...

Mark w dont u think to use this function he has to instal more function other wise the same wont work..???

Saurabh

Click to expand...

Yes, just like MCONCAT which I used earlier.

 

Upvote 0

S

saurabh726

Well-known Member

Joined

Dec 13, 2003

Messages

891

• Jul 26, 2004

• #7

Thats what i thought Mark W.

Saurabh

 

Upvote 0

A

asawhill

New Member

Joined

Jul 26, 2004

Messages

8

• Jul 27, 2004

• #8

Great! That helped a lot! I now have a new hiccup: my new list has multiple instances of the same stock number. Without using any special function add-ins, is there a way to condense this list down to unique values only (example below)? The users of this spreadsheet will not have any special functions on their version of Excel 2002 so the "MONCONCAT" and "UNIQUEVALUES" functions aren't applicable. Thanks.

Stock Number
3103
3103
3546
4587
4587
4587
6257

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jul 27, 2004

• #9

asawhill said:

...my new list has multiple instances of the same stock number. Without using any special function add-ins, is there a way to condense this list down to unique values only (example below)?...

Stock Number
3103
3103
3546
4587
4587
4587
6257

Click to expand...

One option is to invoke Data|Filter|Advanced Filter with the Unique records only option checked. Another is an efficient formula system. Just post back if you need the latter.

 

Upvote 0

A

asawhill

New Member

Joined

Jul 26, 2004

Messages

8

• Jul 27, 2004

• #10

I need to use a formula. Thanks.

 

Upvote 0