0

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.

  • What is in A3 and D3? On which sheet are they found? Are G3 and H3 on the Ranking sheet? – Rey Juna Sep 10 '18 at 17:16
  • OK, I think I see what you have, although I'm assuming you are talking about the formula not just being in row 2 (G3 and H3) but copied down. It looks like it works in row 3, but you need to change this part of your formula: Dashboard!$I$4:I$17143=D3 to have the $ before the 4. With that change it seems to work for every row. Was that your issue? If not, please edit your post to clarify. – Rey Juna Sep 10 '18 at 17:48
  • Hi Rey, thanks but no this wasn't the issue. The issue is that the formula just doesn't seem to work properly. I'm getting some very questionable results from the formula where it's pulling through some EU tours when UK is specified. – RockandGrohl Sep 12 '18 at 13:12
  • I have an example document here which you can play with ufile.io/yhe5s – RockandGrohl Sep 12 '18 at 13:13
  • Your link was blocked, but I think I have an answer anyway (see below). This one made my brain hurt, which usually means that there is a simpler way that I'm overlooking. If my answer doesn't work for you, and if no one else comes up with a better one, then I would suggest posting this in StackOverflow and looking at a VBA solution. – Rey Juna Sep 12 '18 at 20:09
0

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.

  • Lovely, I will try this tomorrow. My solution is as follows, however it is producing a correct result only 75% of the time - sometimes it shows EU tours under UK tour specification, or shows duplicates which shouldn't be happening if it's finding the 1st then 2nd largest. – RockandGrohl Sep 13 '18 at 13:53
  • {=IFERROR(INDEX(Dashboard!$C$4:$C$18295,MATCH(LARGE(IF(Dashboard!$A$4:$A$18295=A3,IF(Dashboard!$H$4:$H$18295=D3,Dashboard!$N$4:$N$18295)),T3),Dashboard!$N$4:$N$18295,0)),"No EU")} – RockandGrohl Sep 13 '18 at 13:53
  • Hi, this works like my result, but it appears to bring up the same issues - duplication. If I take a paper such as "Barrow Evening Mail - Tue" there are 75 possible tours to select. Rank 1 and 2 are fine and select properly the 1st and 2nd highest rank for the tour. This is also the case for Rank 3. Unfortunately, rank 4 is a duplicate of rank 3, even though there is a 4th highest matching criteria on the dashboard. – RockandGrohl Sep 14 '18 at 8:58
  • This will have some issues whenever there are any duplicates, such that in the array {1,2,3,2} 1st will be 3, 2nd will be the first 2, 3rd will also be the first 2, and then 4th will 1. That was what I was trying to say in the Caveat. If you want to ignore duplicates, then I'm not sure if a formula solution will work. You may have to try a VBA solution – Rey Juna Sep 14 '18 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.