Index match

[proficient]

Hi all,

Need to extract correspondence value from a range, my data look like this.

A A1
A A2
A A3
B B1
B B2
B B3

A & B are depart and A1,2,3 and B1,2,3 are designation, what I want is when I'll put A (means depart name) in seperate cell, all correspondence value should show in very next column. Hope it makes sense.

 

[mrhstn]

Do you mean something like this?

Book1
	A	B	C	D	E
1	Dept	Designation		Choose
2	A	A1		B	B1
3	A	A2			B2
4	A	A3			B3
5	B	B1			B5
6	B	B2
7	B	B3
8	A	A4
9	B	B5
Sheet4

Cell Formulas
Range	Formula
E2	=IF(ROWS($D$2:D2)>COUNTIF($A$2:$A$9, $D$2), "",INDEX($B$2:$B$9, AGGREGATE(15, 6, (ROW($A$2:$A$9)-ROW($A$2)+1)/($A$2:$A$9=$D$2), ROWS($D$2:D2))))

 

[proficient]

Works like a charm

 

[mrhstn]

You're very welcome, thanks for the feedback