Hi ,

Please see attached file and kindly advise a formula to look up value using 4 criterias.



Thanks in advance

Dumbo

First, in your Excel sample, remove the space at the end of cell C15
Then, add the following formula to cell C3:

=SUMIFS(D$15:D$500,$A$15:$A$500,$A3,$C$15:$C$500,$ B3,$B$15:$B$500,C$2)

You can then copy this down and over based on the size of your data.

I would recommend keeping your lookup table in another tab and adjusting your formula as follows for cell C3:

=SUMIFS('Look UP Data'!$D:$D,'Look UP Data'!$A:$A,$A3,'Look UP Data'!$C:$C,$B3,'Look UP Data'!$B:$B,C$2)

Doing this now doesn't work because the lookup data isn't the same in the 'Look UP Data' tab.

Note that if you have multiple rows in your lookup data that match the 4 criteria, they will be sum'd up using this solution.

I hope you find this helpful!

Hi,

Am Fri, 9 Nov 2012 04:33:11 +0000 schrieb Excel Dumbo:

Please see attached file and kindly advise a formula to look up value
using 4 criterias.

in C3 try:

=INDEX(D$15:D$16,MATCH($A3&$B3&C$2,$A$15:$A$16&$C$ 15:$C$16&$B$15:$B$16,0))
and enter the array formula with CTRL+Shift+Enter and copy to right and
down.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2