unanswered Removing duplicates in Index match results

10 months 3 weeks ago #1833 by boakwood
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.

Please Log in or Create an account to join the conversation.

Moderators: jaychivoExcelfansqiuhongkun