vlookup one value and return multiple values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I saw the post for mulitple lookups that applies to Excel 2003
http://office.microsoft.com/en-us/excel/HA012260381033.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)
 
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
 
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)*ROW($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
 
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))
 
Back
Top