Multiple Indexing

[LiAD]

Hi,

I would have a list of data such as;

A 4
B 3
C 2
A 5
C 7
A 1

I would like to use some of index and match type function to look through
the list and output the values that correspond to A for example.

So the output would read A 4 5 1 (horizontally)
B 3
C 2 7

Any ideas on what is the best function to use. Index and match I can only
get to pick up the first entry?

 

[Bob Phillips]

use this array formula

=IF(ISERROR(SMALL(IF($A$2:$A$20="A",ROW($B$2:$B$20)),COLUMN(A1))),"",INDEX($B$2:$B$20,SMALL(IF($A$2:$A$20="A",ROW($B$2:$B$20)),COLUMN(A1))-ROW($B$2)+1))

and copy across

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[LiAD]

thanks

perfect

use this array formula

=IF(ISERROR(SMALL(IF($A$2:$A$20="A",ROW($B$2:$B$20)),COLUMN(A1))),"",INDEX($B$2:$B$20,SMALL(IF($A$2:$A$20="A",ROW($B$2:$B$20)),COLUMN(A1))-ROW($B$2)+1))

and copy across

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Data list transform.
No array formulas.
http://www.mediafire.com/file/vwwmitjyn5o/03_18_09.xlsx