Lookup With No Duplicates

[excelbytes]

Can you help me with a formula in column B? I know I've done this a few times before but for the life of me (getting old), can't remember how I did it. The lookup value is in column A. I need to look that value up in column D and return the result from column E. There are obviously duplicates and I need the formula to not duplicate any results.

 

[lrobbo314]

How about this?

Excel
	A	B	C	D	E	F	G
1	Lookup	Output	Formula
2	208	Sample 1	Sample 1			208	Sample 1
3	208	Sample 2	Sample 2			208	Sample 2
4	208	Sample 3	Sample 3			208	Sample 3
5	208	Sample 4	Sample 4			208	Sample 4
6	208	Sample 5	Sample 5			208	Sample 5
7	209	Sample 11	Sample 11			208	Sample 6
8	209	Sample 12	Sample 12			208	Sample 7
9	209	Sample 13	Sample 13			208	Sample 8
10	209	Sample 14	Sample 14			208	Sample 9
11	209	Sample 15	Sample 15			208	Sample 10
12	209	Sample 16	Sample 16			209	Sample 11
13	209	Sample 17	Sample 17			209	Sample 12
14	213	Sample 25	Sample 25			209	Sample 13
15	213	Sample 26	Sample 26			209	Sample 14
16	213	Sample 27	Sample 27			209	Sample 15
17						209	Sample 16
18						209	Sample 17
19						209	Sample 18
20						209	Sample 19
21						209	Sample 20
22						209	Sample 21
23						209	Sample 22
24						209	Sample 23
25						209	Sample 24
26						213	Sample 25
27						213	Sample 26
28						213	Sample 27
29						213	Sample 28
30						213	Sample 29
31						213	Sample 30
32						217	Sample 31
33						217	Sample 32
34						218	Sample 33
Sheet5

Cell Formulas
Range	Formula
G2:G34	G2	="Sample " & SEQUENCE(33)
C2:C16	C2	=INDEX($G$2#,COUNTIF($A$2:A2,A2)+XMATCH(A2,$F$2:$F$34)-1)
Dynamic array formulas.

 

[Peter_SSs]

How about this?

I suspect that column G may well not be a spilled array, so perhaps this modification.

=INDEX(G$2:G$34,COUNTIF(A$2:A2,A2)+XMATCH(A2,F$2:F$34)-1)

@excelbytes
If column E is always grouped like your sample then one of the above formulas should be fine (adjusting to your columns)
I have included another option below, in case column E may not always be grouped.

24 04 08.xlsm
	A	B	C	D	E	F
1	Lookup	Formula
2	208	Sample 4			209	Sample 1
3	208	Sample 7			217	Sample 2
4	208	Sample 9			209	Sample 3
5	208	Sample 10			208	Sample 4
6	208	Sample 16			209	Sample 5
7	209	Sample 1			209	Sample 6
8	209	Sample 3			208	Sample 7
9	209	Sample 5			213	Sample 8
10	209	Sample 6			208	Sample 9
11	209	Sample 11			208	Sample 10
12	209	Sample 14			209	Sample 11
13	209	Sample 15			218	Sample 12
14	213	Sample 8			213	Sample 13
15	213	Sample 13			209	Sample 14
16	213	Sample 25			209	Sample 15
17					208	Sample 16
18					209	Sample 17
19					209	Sample 18
20					208	Sample 19
21					209	Sample 20
22					217	Sample 21
23					208	Sample 22
24					208	Sample 23
25					208	Sample 24
26					213	Sample 25
27					213	Sample 26
28					213	Sample 27
29					208	Sample 28
30					209	Sample 29
31					209	Sample 30
32					209	Sample 31
33					209	Sample 32
34					213	Sample 33
Lookup (2)

Cell Formulas
Range	Formula
B2:B16	B2	=INDEX(F:F,AGGREGATE(15,6,ROW(E$2:E$34)/(E$2:E$34=A2),COUNTIF(A$2:A2,A2)))