LOOKUP IF matches multiple values

[packet]

Hi, I am trying to do a lookup based on 2 values matching, ive tried numerous formulas (index, match, sumproduct) etc but cant get it to work as im looking up text data.

I have a row of data across the top

e.g. Barnet, Cheltenham etc

I have a column of dates along the side
06/08
20/08
etc

I then want to lookup column 7 in another worksheet but only if column 1 matches my row of data (e.g. Barnet) and Column 2 matches my column of data (e.g. 06/08)

Can anyone help me please!!!!

 

[Andrew Poulsom]

If your towns start in B1, your dates start in A2 and your table contains only unique combonations of town and date, in B2:

=SUMPRODUCT(--(Sheet2!$A$1:$A$100=B$1),--(Sheet2!$B$1:$B$100=$A2),Sheet2!$G$1:$G$100)

copied down and across should do the job.

 

[packet]

Thanks for that, tried it but doesnt seem to work, although it returns a value of 0 instead of an error message, I can see that the array has matched the 2 values but its not returning the lookup value in column G

Any ideas?

 

[Richard Schollar]

Does Column G contain text rather than a genuine number?

 

[packet]

yep it contains text