Index/Match with IF Statement

Question

As you can see, I have a database table on the left. And I want to add in IF statement that allows me to lookup the [Code], [Name] and [Amount] of the top 5 of Company A ONLY. Then do a top 5 for Company B and so on. I have managed to lookup the top 5 out of ALL companies but cannot seem to add a criteria to target specific company.

Here are my formulas so far:

Formula in Column K [Company]: = INDEX(Database,MATCH(N3,sales,0),1)

Formula in Column L [Code]: = INDEX(Database,MATCH(N3,sales,0),2)

Formula in Column M [Name]: = INDEX(Database,MATCH(N3,sales,0),2)

Formula in Column N [Amount]: = LARGE(sales,ROW(1:20))

The intended result is to show the top 5 sales person in each company along with their [Code], [Name] and [Amount], feel free to suggest any edits to the worksheet.

Answer 1

Here's an alternative if you know the code is unique. After putting A into K3:K7

First get the highest amounts for Company A starting in N3

=AGGREGATE(14,6,Database[Amount]/(Database[Company]=K3),ROWS(N$1:N1))

Then find the code which matches the amount, but only if it hasn't been used before (this assumes that the code is unique) starting in L3

=INDEX(Database[Code],MATCH(1,INDEX((Database[Company]="A")*(Database[Amount]=N3)*ISNA(MATCH(Database[Code],L$2:L2,0)),0),0))

Then find the matching name with a normal INDEX/MATCH starting in M3

=INDEX(Database[Name],MATCH(L3,Database[Code],0))

Answer 2

Okay, I have achieved this with the use of a helper column which you can hide. Please nnote though that this will only work as long as there are not more than 9 identical totals for any 1 company, I don't think you should have that issue but it may occur, the digits being added by the helper column would need to be tweaked

First Helper Column:

Adds a digit to the end of the total representing the number of times that amount already exists above for that company. This formula is =CONCATENATE([@Amount],COUNTIFS($A$1:A1,A2,$D$1:D1,D2))*1

This is multiplied by 1 to keep the number format for LARGEto work with.

Second Helper Column:

This is an array formula and will need to be input by using Ctrl+Shift+Enter while still in the formula bar.

The formula for this one is: =LARGE(IF(Company="A",Helper),ROW(1:1))

What this formula does as an array formula is produce a list of results based on the IF statement that LARGE can use. Rather than the entire column being ranked largest to smallest, we can now single out the rows that have company "A" like so:

=LARGE({20000;20001;20002;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;15000;14000;30000;FALSE;FALSE;FALSE;FALSE},ROW(1:1))

LARGE will only work with numeric values so the FALSES produced where column A does not match "A" will be ignored. Notice why I have used the helper column here to eliminate unique values but not affect the top 5.

ROW(1:1) has been used as this will automatically update when the formula is dragged down to produce the next highest result in this array.

The main formula for top 5 array

Again this is an Array formula so will need to be input by using Ctrl+Shift+Enter while still in the formula bar.

=INDEX(Database,SMALL(IF(Company="A",IF(Helper=$O3,ROW(Company))),1)-1,COLUMN(A:A))

With array formulas for some unknown reason IF(AND()) just does not work for me so I have nested two IF's instead.

Notice how I am again checking whether the first column matches "A" and then whether the last column matches the result of the second formula. What will happen is where both of these conditions match in the array (as in both produce TRUE for the same row) I wanted the row number to be returned.

IF({TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE},IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE},{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}))

It looks like a mess I know, but the position where both TRUEs align gives us the row 16 as a result.

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;16;FALSE;FALSE;FALSE;FALSE}

As I know that there can only be one match possible for this, I use SMALL to grab the first smallest number to use in the INDEX formula for row and deduct 1 as we are not considering the headers in the INDEX formula so we actually want the 15th result.

Again, COLUMN(A:A) has been used for the column number to return as this will automatically update when the formula is dragged across.

If you are struggling with my explanation and want me to provide more clarity, feel free to reach out and I will try my best to explain the logic in more detail