LOOKUP IF matches multiple values

packet

New Member
Joined
Aug 5, 2004
Messages
34
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!!!!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Aug 5, 2004
Messages
34
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?
 

Forum statistics

Threads
1,137,060
Messages
5,679,376
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top