Tip: Other languages are Google-Translated. You can visit the English version of this link.
or

Register

or

# Removing duplicates in Index match results

2 months 3 weeks ago #1833 by boakwood
Hi
Hopefully someone will be able to assist with this... because it's driving me mad.
I have a summary sheet called Targetted where I am trying to collate results, currently I am using the small function with criteria to pull the lowest values for each sales agent this pulls from a separate sheet called Rev 3
{=SMALL(IF('Rev 3'!\$G:\$G=Targetted!\$B\$3,'Rev 3'!\$AH:\$AH),Targetted!A56)}
Rev 3 Column G is the agent name
Targetted B3 is agent name
Rev 3 AH is the value
Targetted A56 is the rank
This formula works fine and pulls the lowest 20 results
I then use the following formula to pull through the relevant company name
{=INDEX('Rev 3'!\$B:\$B,MATCH(1,('Rev 3'!\$AH:\$AH=G56)*('Rev 3'!\$G:\$G=Targetted!\$B\$3),0))}
Rev 3 column B is the company name
G56 is the result from the small function.
The issue I have is that the result for the company where I have the same value on more than one row is always the first company name the formula finds.
Any help would be greatly appreciated.