Multiple vlookup/index match issue

[Graham Cobden]

Hi All,

I am trying to discover how to do a lookup to a cell with 2 criteria....easy I hear you say....well.....

I have a set of data over 5 columns of which the 2 criteria are in columns A and B. In column A are the names of companies which sometimes appear each day and sometimes do not. In column B in the date that links to the company name. In column E is the contact rate % which I want to 'look up'. Essentially I am trying to create a formula which I can drag into the table on the right which essentially says that if the date = 01/04/2015 and the company = leads wizard, pull the contact % (which is 100%). I want this formula to then be able to drag down so it looks up a different company name but still for the first. I have achieved this but the issue comes when I want to drag the formula across to the right into the 02/04/2015 column. The formula returns #NA and errors no matter what method I try be it index match or multiple lookup or using ctrl alt and enter.

Any help would be greately appreciated!!

 

[tomas]

Hi

Use this formula in cell I3 and copy across

=SUMPRODUCT((I$2=$C$3:$C$520)*($H3=$B$3:$B$520)*($F$3:$F$520))

 

[Khalid NGO]

Hi Graham and welcome to the forum Chandoo.org.

You can also use this with CSE:

=SUM(IF($B$3:$B$520=$H3,IF($C$3:$C$520=I$2,$F$3:$F$520)))

Enter with Ctrl+Shift+Enter

Copy down and across...

Regards,