# 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

## Files

• 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

• Re: Multiple criteria wit INDEX

THANK YOU! Extremly thankful! amny many thanks. you are the best

## Participate now!

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