question between sumifs and index with match

[sleepingboy]

Hi guys,

i am trying to do lookup with more than 1 criteria.
Which is better, sumifs or index with match.

I do upload my sample file
A:N is the raw data, while P : Q is the data that i do the lookup.

However,
in cell: Q3: =INDEX(B3:N4,MATCH(P3,A3:A5,0),MATCH(Q1&Q2,B1:N5&B2:N5,0)) &
in cell :Q4: =SUMIFS(B1:N4,B1:N1,Q1,B2:N2,Q2,A3:A4,P3)

both are error(#VALUE!), and I have no idea about them.

wish someone can help me

thx thx

 

[Nebu]

Hi:

Your index match array formula should be like

=INDEX($B$3:$N$4,MATCH($P$3,$A$3:$A$5,0),MATCH($Q$1&$Q$2,$B$1:$N$1&$B$2:$N$2,0))

, The cell reference you had given in your formula is wrong.

Sumifs will not really work if the range sizes are different, hence you are getting an error

Instead of sumifs you can use the following array formula

=SUM(IF($B$1:$N$1=Q1,IF($Q$2=$B$2:$N$2,IF($P$3=$A$3:$A$4,$B$3:$N$4))))

Thanks

 

[sleepingboy]

THX, however, it does not work

 

[Deepak]

THX, however, it does not work

Then will u pls share the expected output.

You might not use nebu's formula with CSE.

Meanwhile try this

=INDEX($A$1:$N$4,MATCH($P$3,$A$1:$A$4,0),LOOKUP(2,1/($A$1:$N$1&$A$2:$N$2=$Q$1&$Q$2),COLUMN($A$1:$N$1)))

 

[sleepingboy]

thx deepak,

What i expected in Q3 is 653.

 

[Faseeh]

Hi Sleepingboy

Try:

=SUMPRODUCT(($A$3:$A$4=P3)*($B$1:$N$1&$B$2:$N$2=$Q$1&$Q$2)*($B$3:$N$4))

....drag down

or for Index Match Case you should use this with CSE

=INDEX($B$3:$N$4,MATCH(P3,$A$3:$A$4,0),MATCH($Q$1&$Q$2,$B$1:$N$1&$B$2:$N$2,0))

...drag down.

 

[sleepingboy]

thx Faseeh,

It works, but may i know the reason for using sumproduct?
(As i wish to learn )

 

[Faseeh]

I just edited post Index Match will also work.

 

[jeffreyweir]

I would never use a SUMPRODUCT in a case like this. Just use a PivotTable. Much easier for someone else to understand, and much more efficient.

 

[Faseeh]

@jeffreyweir,

Most of the time as i present report to boss he is more interested in black and white lines and his required layout printed on a paper rather then SUMPRODUCT or INDEX MATCH or PIVOT TABLES behind them.

If i try to explain him the difference in between them, he will ask me to keep the details to me and hand over the paper to him in required format. PIVOT TABLE might be the best solution but at times, you have to listen to a boss who don't know any thing about pivots and neither is interested in updating his knowledge.

 

[jeffreyweir]

If for some reason I couldn't use a PivotTable directly because of some formatting issue, then I'd either format my PivotTable so it didn't look like a PivotTable or I'd use GETPIVOTDATA to fetch the data from the PivotTable into whatever format the boss wants.

The boss wouldn't be any the wiser. But the next analyst who inherits your work would be. Because a PivotTable is about as simple a number-crunching device as Excel has. And SUMPRODUCT is horribly inefficient and complicated by comparison. As is any CSE formula.

 

[Deepak]

@jeffreyweir,
.............., you have to listen to a boss who don't know any thing about pivots and neither is interested in updating his knowledge.

Well said!!