Multiple criteria wit INDEX

  • Hi everyone,


    I have been trying for hours but can't get it right.
    I have a table with the following (its actually more but for this purpose this is enough):


    ColumnA ColumnB ColumnC
    Newspaper Product Publish date


    It's a long list, working sheet, so when ever a product is published it is added, so there is no order in it.
    Since there is alot of other data in the working sheet, such as price, I need every input on a seperate row, especially to get my pivot table working


    What i want is to see the publishing dates for every newspaper and product on a row, like a pivot table:


    Newspaper 1 - Product A - Date1 -Date2- Date3 -Date4 and so on
    Newspaper 1 - Product B - Date1 -Date2- Date3 -Date4 and so on
    Newspaper 2 - Product A - Date1 -Date2- Date3 -Date4 and so on
    ....


    Started with this:
    INDEX($A$4:$C$34,MATCH(1,($A$2:$A$34=$E2)*($B$2:$B$34=$F2),0),3), but this will only return the first pulishing date, i need all


    so after a lot of work I ended up with this, but i loose the multiple criteria:
    {INDEX($A$2:$C$34,MIN(IF($A$2:$A$34=$E2,ROW($B$2:$B$34)),ROW(1:1))-1,3)}


    E2 is searchcriteria for column a newspaper..
    F2 is searchcriteria for column b product..


    I need to put in the product criteria as well but i can't get it rigt. Tried with AND after IF but nope...


    The original list is over 10000rows so i realy need a working function


    Hope someone can help


    PS The attached file is swedish excel so mainly , is ; MIN is MINSTA IF is OM



    Thanks

  • Re: Multiple criteria wit INDEX


    one approach


    =IFERROR(INDEX($C$1:$C$34,LARGE(ROW($A$2:$A$34)*(($A$2:$A$34)=$E2)*(($B$2:$B$34)=$F2),SUMPRODUCT((($A$2:$A$34)=$E2)*(($B$2:$B$34)=$F2))-(COLUMNS($G$1:G$1)-1))-ROWS($C$2)+1),"")


    note this is an array formula and needs to be conformed by Ctrl+Shift+Enter

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!