Multiple Vlookups #num! Error

[nehpets12]

What is wrong with this formula? I get a #num! Error

Vliookup differant rows.xls
	A	B	C	D	E	F	G
17	Steve	1		Steve		1
18	Steve	3		Darren		2
19	Steve	5		Steve		3
20	Steve	#NUM!		Darren		4
21	Steve	#NUM!		Steve		5
22	Steve	#NUM!		Darren		6
23	Steve	#NUM!		Steve		7
24	Steve	#NUM!		Darren		8
25	Steve	#NUM!		Steve		9
26	Steve	#NUM!		Darren		10
27				Steve		11
28				Darren		12
29				Steve		13
30				Darren		14
31				Steve		15
32				Darren		16
33				Steve		17
34				Darren		18
35				Steve		19
36				Darren		20
37				Steve		21
38				Darren		22
39				Steve		23
40				Darren		24
41				Steve		25
42				Darren		26
Sheet1

 

[iliace]

I think D17:D22 should be D17:D42 instead.

 

[nehpets12]

Thank you ... I should have spotted that

Thanks again

 

[hardeep.kanwar]

Try This

INDEX($D$17:$G$42,SMALL(IF($D$17:$D$42=$A17,ROW($D$17:$D$42)-ROW($D$17)+1),COUNTIF($A$17:$A17,A17)),4)


Hardeep kanwar