On Thursday, July 12, 2012 7:43:27 PM UTC-5, Excel Dumbo wrote:
Hello Dear Excel Mates,

I have a table (attached) where i need to to auto populate Net Sales
values with multiple conditions. This is tearing my brain down. I hope
the excel mates in here can help me solve this.

much appreciated


+-------------------------------------------------------------------+
|Filename: Complex Look UP.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=467|
+-------------------------------------------------------------------+



--
Excel Dumbo

Put identifiable month in row 1
1.2012 2.2012 3.2012
Jan Feb Mar

for e7copy across
=SUMPRODUCT(--('LookUP Data'!$A$2:$A$300=E$1)*--(RIGHT('LookUP Data'!$E$2:$E$300,4)="tips")*'LookUP Data'!$I$2:$I$300)

For c4, etc copy across & down

=SUMPRODUCT(--('LookUP Data'!$A$2:$A$29=E$1)*--('LookUP Data'!$E$2:$E$29=$C4),'LookUP Data'!$I$2:$I$29)