Hello,
I'm curious about whether there is a benefit in using a "*" to effectively separate / multiply criteria in IF functions vs nesting.
For example...
=INDEX(Table1[Color],MATCH(MAX( IF ( Table1[Item]="T-shirt" , IF (Table1[Size]="Large" , Table1[Time] ) ) ),Table1[Time],0))
vs
=INDEX(Table1[Color],MATCH(MAX( IF ( (Table1[Item]="T-shirt") * (Table1[Size]="Large"), Table1[Time] ) ) ,Table1[Time],0))
...seem to produce the same result, but is one any more efficient that another? Or is there some other relative benefit?
I have a table of ~8k rows and 45+ columns to search and ~35 of these formulae to populate another table, and sometimes 2-3 of these themselves subject to IF criteria in the same formula.
I have to stick to things that work for Excel 2010 so can't later alternatives.
I'm curious about whether there is a benefit in using a "*" to effectively separate / multiply criteria in IF functions vs nesting.
For example...
=INDEX(Table1[Color],MATCH(MAX( IF ( Table1[Item]="T-shirt" , IF (Table1[Size]="Large" , Table1[Time] ) ) ),Table1[Time],0))
vs
=INDEX(Table1[Color],MATCH(MAX( IF ( (Table1[Item]="T-shirt") * (Table1[Size]="Large"), Table1[Time] ) ) ,Table1[Time],0))
...seem to produce the same result, but is one any more efficient that another? Or is there some other relative benefit?
I have a table of ~8k rows and 45+ columns to search and ~35 of these formulae to populate another table, and sometimes 2-3 of these themselves subject to IF criteria in the same formula.
I have to stick to things that work for Excel 2010 so can't later alternatives.