Retrieve Data From List With Duplicate Information

[bjrich9]

Hello everyone!

So heres my question...i have not been able to figure it out!

I have a list of data, one column has service offerings, the others has customers served. I need to pull the top jobs and the bottom jobs. For the bottom, i sorted the list by customers served and used an HLOOKUP to retrieve the bottom three. The top three jobs are at the bottom and im currently using Large to find top three Customers Served fields, then a Match and finally an Index to pull the Service name.

My problem is this. If there are multiple jobs, at the top, with the same Customers Served number, it is pulling duplicates and not the desired data.

Service	Customers
Pets	1
Building	1
Bylaw	1
Development	2
Business Change	3
Business Development	4
Alterations	5
Encroachment	5
Compliance	6
Accessory Building	6
Permits	8

<tbody>
</tbody>

I'd like it to pull: Permits, Accessory Building and Compliance...however it is returning: Permits, Compliance, Compliance


Any help will be much obliged!!!

cheers

 

[Marcelo Branco]

Maybe something like

	A​	B​	C​	D​	E​	F​
1​	Service​	Customers​		Top​	Service​	Customers​
2​	Pets​	1​		1​	Permits​	8​
3​	Building​	1​		2​	Accessory Building​	6​
4​	Bylaw​	1​		3​	Compliance​	6​
5​	Development​	2​
6​	Business Change​	3​
7​	Business Development​	4​
8​	Alterations​	5​
9​	Encroachment​	5​
10​	Compliance​	6​
11​	Accessory Building​	6​
12​	Permits​	8​

Formula in E2 copied down
=INDEX($A$2:$A$12,COUNTA($A$2:$A$12)-D2+1)

Formula in F2 copied down
=VLOOKUP(E2,A:B,2,0)

Hope this helps

M.

 

[bjrich9]

Wow!


thank you so much!

a song for you! haha

https://www.youtube.com/watch?v=bMbtzalS3u8