vendredi 26 octobre 2018

Index with multiple criteria and giving multiple results

I have a large excel table about 30 columns (main table) and I want to make reports from that table in separate sheets. For each report, there are some conditions to filter in order to bring the data from the main table, I use If statement with "AND, OR" logic but I got many blank rows and I know I can hide them but each time I change the conditions I should hide the blank rows again, So I use INDEX to get rid of this issue.

The formula worked fine for single criteria and get me all the results without any blank rows, but when I tried to use it with multiple conditions it is not working.

I need your help to implement the first formula into the second formula.

The If statement formula is

=IFERROR(IF(OR(AND($P$1<>"",$P$1=Table10[Ship st.],$P$2=""),AND($P$2<>"",$P$2=Table10[Forwarder],$P$1=""),AND($P$1=Table10[Ship st.],$P$2=Table10[Forwarder]),AND($P$3=Table10[Requesed by],$P$1=Table10[Ship st.])),Table10[Subject],""),"")

Where P1, P2 & P3 are the criteria I look for in Table10.

The Index formula is

=IFERROR(INDEX(Table10[Subject],AGGREGATE(15,3,(Table10[Forwarder]=$P$1)/(Table10[Forwarder]=$P$1)*(ROW(Table10[Forwarder])-ROW(Table10[[#Headers],[Forwarder]])),ROWS($B$3:$B3))),"")

Aucun commentaire:

Enregistrer un commentaire