vlookup one value and return multiple values
 

I saw the post for mulitple lookups that applies to Excel 2003
http://office.microsoft.com/en-us/ex...260381033.aspx

but I can't seem to get it to work for the 2002 version of Excel. Please
advise.

Here is what I have.

A B
1 Product ID
2 Ice-cream 234
3 Coffee 334
4 TEA 434
5 Milk 534
6 OJ 634
7 TEA 734
8 TEA 834

I want to be able to look up product tea. and have return value 434,734,834.
on sepearate rows. I tried the following forumala that was recommeded from
the above link, but get #Value error. What am I doing wrong? Does this not
work because I have Excel 2002, how do I get this to work?

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),2)

vlookup one value and return multiple values
 

If you were in a position to use the AutoFilter instead of the VLOOKUP
function, it would nicely display the results you seek.

Vaya con Dios,
Chuck, CABGx3



"Lisa" wrote:

I saw the post for mulitple lookups that applies to Excel 2003
http://office.microsoft.com/en-us/ex...260381033.aspx

but I can't seem to get it to work for the 2002 version of Excel. Please
advise.

Here is what I have.

A B
1 Product ID
2 Ice-cream 234
3 Coffee 334
4 TEA 434
5 Milk 534
6 OJ 634
7 TEA 734
8 TEA 834

I want to be able to look up product tea. and have return value 434,734,834.
on sepearate rows. I tried the following forumala that was recommeded from
the above link, but get #Value error. What am I doing wrong? Does this not
work because I have Excel 2002, how do I get this to work?

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),2)

vlookup one value and return multiple values
 

With these values beginning in A1
Product ID
Ice-cream 234
Coffee 334
TEA 434
Milk 534
OJ 634
TEA 734
TEA 834

and
D1: TEA

Try one of these formulas:

E1:
=INDEX($B$1:$B$10,SMALL(INDEX(($A$1:$A$10=$D$1)*RO W($A$1:$A$10)+($A$1:$A$10<$D$1)*10^99,0),ROW()))
Copy E1 down as far as you need.

OR
....this shorter ARRAY FORMULA
(which you commit with CTRL+SHIFT+ENTER, instead of just ENTER)

E1: =INDEX($B$1:$B$10,SMALL(IF(($A$1:$A$10=$D$1),ROW($ A$1:$A$10)),ROW()))

Copy E1 into E2 and down as far as you need

Does that help?
(post back with more questions)
***********
Regards,
Ron

XL2002, WinXP


"Lisa" wrote:

I saw the post for mulitple lookups that applies to Excel 2003
http://office.microsoft.com/en-us/ex...260381033.aspx

but I can't seem to get it to work for the 2002 version of Excel. Please
advise.

Here is what I have.

A B
1 Product ID
2 Ice-cream 234
3 Coffee 334
4 TEA 434
5 Milk 534
6 OJ 634
7 TEA 734
8 TEA 834

I want to be able to look up product tea. and have return value 434,734,834.
on sepearate rows. I tried the following forumala that was recommeded from
the above link, but get #Value error. What am I doing wrong? Does this not
work because I have Excel 2002, how do I get this to work?

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),2)

vlookup one value and return multiple values
 

This placed in C2 and copied down would give you the total for each item...

=A1&" "&(SUMIF($A$1:$A$10,A1,$B$1:$B$10))

"CLR" wrote:

If you were in a position to use the AutoFilter instead of the VLOOKUP
function, it would nicely display the results you seek.

Vaya con Dios,
Chuck, CABGx3



"Lisa" wrote:

I saw the post for mulitple lookups that applies to Excel 2003
http://office.microsoft.com/en-us/ex...260381033.aspx

but I can't seem to get it to work for the 2002 version of Excel. Please
advise.

Here is what I have.

A B
1 Product ID
2 Ice-cream 234
3 Coffee 334
4 TEA 434
5 Milk 534
6 OJ 634
7 TEA 734
8 TEA 834

I want to be able to look up product tea. and have return value 434,734,834.
on sepearate rows. I tried the following forumala that was recommeded from
the above link, but get #Value error. What am I doing wrong? Does this not
work because I have Excel 2002, how do I get this to work?

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),2)