Excel – Index Match + Large with 2 Criteria

microsoft excel

similar questions have been asked on this lovely forum but none of them apply to my problem even after a bit of jiggery-pokery on my end.

I have two sheets (Ranking, Dashboard) and I need to find the top 'k' value (1st, 2nd, 3rd, 4th etc) for each criteria.

On 'Ranking' sheet, I have a column in A which lists newspapers. In Column D, there is a criteria, such as "UK", "EU" and "Promo".

In G3 I have the number that represents the 'k' value for the LARGE formula – in this case, it's 1

I'm placing the formula in H3.

{=INDEX(Dashboard!$D$4:$D$17143,MATCH(1,INDEX((LARGE(IF(Dashboard!$A$4:$A$17143=A3,Dashboard!$O$4:$O$17143),G3)=Dashboard!$O$4:$O$17143)(Dashboard!$A$4:$A$17143=A3)(Dashboard!$I4:I$17143=D3),0),0))}

The explanation is:

Dashboard D:D is the Advert Tour Reference – this is what I want brought back.

Dashboard A:A is the list of 17,000 paper names (320 unique values)

Dashboard O:O is the score of each advert for each paper – the best 4 are what I want.

Dashboard I:I is the type of tour (UK, EU, Promo)

What I would like is for the formula to look on the dashboard for the top k rank that satisfies the Paper Name being the same, the tour type being the same as specified and the score being the same k as specified.

Any ideas? Thank you.

Best Answer

After playing around with this for a while, I found issues when the result found with LARGE using the name (Dashboard A:A) didn't match the type (Dashboard I:I). I think the finding the large value is just the first step which has to be used as part of your matching criteria, which is really name, type, and score. Try this array formula instead:

{=INDEX(Dashboard!$D$4:$D$17143,MATCH(1,(A3=Dashboard!$A$4:$A$17143)*(D3=Dashboard!$I$4:$I$17143)*(LARGE((Dashboard!$O$4:$O$17143)*(A3=Dashboard!$A$4:$A$17143)*(D3=Dashboard!$I$4:$I$17143),G3)=Dashboard!$O$4:$O$17143),0),0)}

Note: Don't forget to enter as Ctrl-Shft-Enter.

LARGE((Dashboard!$O$4:$O$17143)*(A3=Dashboard!$A$4:$A$17143)*(D3=Dashboard!$I$4:$I$17143),G3) returns the nth (G3) largest value from the array that matches the name and type.

Now that you have the LARGE value, you have all 3 criteria necessary to get your match: name, type, and score.

MATCH looks for the TRUE in the matrix multiplication of the arrays that match the name, type, and score (Dashboard O:O) and gives the index to the Advert tour reference (Dashboard D:D) row.

Caveat: Not sure if it would matter for your use, but if you have more than one row that matches name, type, and score, this will only return the first one and you won't know that there may have been other matches.

Related Question