lookup similar lookup value but with a different return value

[ExcelNewbie2020]

I have a source data (code column) that has duplicate values with different names.. Would it be possible for a lookup function to look for similar value but the different return value. Please find below expected result.. thank you.. (Excel 2021)

source​				expected result​
Code​	Name​			Code​	Name​
AAA​	Lara			AAA​	Lara​
BBB​	Gayle			AAA​	Welles​
CCC​	Ponting			BBB​	Gayle​
AAA​	Welles			BBB​	Perot​
BBB​	Perot			CCC​	Ponting​
CCC​	Murdoch			CCC​	Murdoch​
DDD​	Kipling			DDD​	Kipling​
EEE​	Coolidge			EEE​	Coolidge​
EEE​	Porter			EEE​	Porter​
GGG​	Connery			GGG​	Connery​
HHH​	Beatty			GGG​	Fenty​
GGG​	Fenty			HHH​	Beatty​

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	source				expected result
2	Code	Name			Code	Name
3	AAA	Lara			AAA	Lara
4	BBB	Gayle			AAA	Welles
5	CCC	Ponting			BBB	Gayle
6	AAA	Welles			BBB	Perot
7	BBB	Perot			CCC	Ponting
8	CCC	Murdoch			CCC	Murdoch
9	DDD	Kipling			DDD	Kipling
10	EEE	Coolidge			EEE	Coolidge
11	EEE	Porter			EEE	Porter
12	GGG	Connery			GGG	Connery
13	HHH	Beatty			GGG	Fenty
14	GGG	Fenty			HHH	Beatty
Summary

Cell Formulas
Range	Formula
F3:F14	F3	=INDEX(FILTER($B$3:$B$14,$A$3:$A$14=E3),COUNTIFS(E$3:E3,E3))

 

[ExcelNewbie2020]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	source				expected result
2	Code	Name			Code	Name
3	AAA	Lara			AAA	Lara
4	BBB	Gayle			AAA	Welles
5	CCC	Ponting			BBB	Gayle
6	AAA	Welles			BBB	Perot
7	BBB	Perot			CCC	Ponting
8	CCC	Murdoch			CCC	Murdoch
9	DDD	Kipling			DDD	Kipling
10	EEE	Coolidge			EEE	Coolidge
11	EEE	Porter			EEE	Porter
12	GGG	Connery			GGG	Connery
13	HHH	Beatty			GGG	Fenty
14	GGG	Fenty			HHH	Beatty
Summary

Cell Formulas
Range	Formula
F3:F14	F3	=INDEX(FILTER($B$3:$B$14,$A$3:$A$14=E3),COUNTIFS(E$3:E3,E3))

worx like a charm.. thank you..

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ExcelNewbie2020]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	source				expected result
2	Code	Name			Code	Name
3	AAA	Lara			AAA	Lara
4	BBB	Gayle			AAA	Welles
5	CCC	Ponting			BBB	Gayle
6	AAA	Welles			BBB	Perot
7	BBB	Perot			CCC	Ponting
8	CCC	Murdoch			CCC	Murdoch
9	DDD	Kipling			DDD	Kipling
10	EEE	Coolidge			EEE	Coolidge
11	EEE	Porter			EEE	Porter
12	GGG	Connery			GGG	Connery
13	HHH	Beatty			GGG	Fenty
14	GGG	Fenty			HHH	Beatty
Summary

Cell Formulas
Range	Formula
F3:F14	F3	=INDEX(FILTER($B$3:$B$14,$A$3:$A$14=E3),COUNTIFS(E$3:E3,E3))

sir, i am just curious if there is similar formula that can be used for excel 2019 user? thanks

 

[Fluff]

How about

=INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$100)/($A$3:$A$100=E3),COUNTIFS(E$3:E3,E3)))