Two-Column Lookup

I've googled around for the answer on this and the solutions I've found have been unsatisfactory or too specific to be of much help in my case, so hopefully this will help explain correctly what I need to do.

I need to reflect a units number from my internal database table within an ad campaign report in another table, so I can see what my cost per acquisition is.

The table with my units number looks like this:

Please Login or Register  to view this content.

and my other table, my campaign report looks like this:

Please Login or Register  to view this content.

I added column F to the second table to reflect number of units from the first table.

I want to put a formula in column F where it will look up the two variables within the same (campaign report) table, Date and Source, then go to my internal database table, look up the line that has the match for the same date/source, and pull the appropriate units number into the campaign table. So in my campaign table, column F2 would pull the number 68 from the internal table because there's a match on both "1/2/07" and "CBS"; columns F3 would pull 72, and column F4 would pull 47.

Of course, I want to replicate this function between two tables with tens of thousands of rows!

Can anybody help me with this? I really appreciate it if you can.

Chuck

Hi everyone -- update:

I tried all the solutions, and the one that seemed to work was the Index/Match array formula solution.

The problem is that it takes my Excel sheet forever to calculate the array formula, and it could only do a few hundred rows at a time without crashing (I have 28,000 rows and growing).

So I came up with, for my purposes, a genius if unsustainable solution.

I created a pivot table of the information, with the sources in rows and the dates in columns. Above the pivot table columns, I labeled each date column with a column number -- 2, 3, 4, so on.

Next to the PVT I created a simple two column table of date in one column, the column number in the other.

In my data table sheet, I created two VLOOKUP formulas. The first one picks up the column number from the two-column table I just created. And the second VLOOKUP uses the resulting column number from the first VLOOKUP to deternmine which column to look up in the pivot table.

So, in a PVT starting with 1/1/07 in the sheet named "VLOOKUPS by date", 1/1 is column number 2, 1/2 is #3, 1/3 is #4, so on.

To get the number for ABC on 2/11, in cell L27373 in the sheet named "data", the first VLOOKUP formula I use is

=VLOOKUP(A27373,'VLOOKUPS by date'!CU$3:CV$97,2)

which returns the number #43, the column number assigned to 2/11/07. Then in the second VLOOKUP, in cell M27373, it uses that result:

=VLOOKUP(D27373,'VLOOKUPS by date'!A$2:CR$911,data!L27373,FALSE)

And I get the data I am looking for!

So in effect, I am using two VLOOKUP tables to do the work. Instead of the sheet crashing, I was able to calculate the registrations in 28,000 rows in less than 10 seconds.

Thanks again for everyone's help!

Chuck