1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel - Index Match + Large with 2 Criteria

Discussion in 'Main Forum' started by RockandGrohl, Sep 12, 2018.

  1. RockandGrohl

    RockandGrohl Guest

    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.

    Login To add answer/comment
     

Share This Page