lookup one value in list and return multiple results


O

ORLANDO V

Hi,

Here is an illustrative example of my problem:

I have a list of numbers and letters. How can I lookup the value " 1 " and
return the multiple results of z, a, g ? I want to type only one formula
in one cell, although I don't mind the result being spread out over multiple
cells. Which formula can be used?
Thanks for your thoughts on this.

1 z
2 b
1 a
3 e
4 f
1 g
 
Ad

Advertisements

R

Ron Coderre

With your posted sample in Cells A1:B6

Try this:
C1: (the Col_A value to find....eg 1)

Enter this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER,
instead of just ENTER....in cell:
Note: Formula is in sections for readability:
D1: =IF(ROWS($1:1)>COUNTIF($A$1:$A$10,$C$1),"",
INDEX($B$1:$B$10,SMALL(IF(($A$1:$A$10=$C$1),ROW($A$1:$A$10)),ROWS($1:1))))

Copy D1 and paste into D2:D5

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Ad

Advertisements

R

RagDyer

So, are you saying that you *don't* want to enter a formula in a cell and
then copy it down the column?
 

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

Top