Look UP using 4 criterias

[Excel Dumbo]

Hi ,

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



Thanks in advance

Dumb

+-------------------------------------------------------------------
|Filename: Look up using 4 criterias.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=672
+-------------------------------------------------------------------

 

[CrackerJack]

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$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 adjustin
your formula as follows for cell C3:

=SUMIFS('Look UP Data'!$D:$D,'Look UP Data'!$A:$A,$A3,'Look U
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 i
the 'Look UP Data' tab.

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

I hope you find this helpful

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

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$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