Tuesday, 17 September 2019
  0 Replies
  3.1K Visits
0
Votes
Undo
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.
There are no replies made for this post yet.