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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,218,539
Messages
6,143,082
Members
450,461
Latest member
Bosavon

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
Back
Top