Double vlookup for table array with dates

I have a table that shows a row of dates, a row of campaigns, and then a row of values I need to reference. I need to pull the values for a specific date and corresponding campaign.

For example, I need to pull the value for campaign 'notset' on 6/16/09.

I tried the following, but it would only work on the first and last campaign:

=IF(VLOOKUP($A3,Campaign!$A:$F,COLUMN(Campaign!$B:$B),FALSE)="notset",VLOOKUP($A3,Campaign!$A$14:$F$23,COLUMN(Campaign!$C:$C), FALSE),"IDIOT")

$A3 was the date I was referencing.

Excel gods, please help me here! TIA!

Date in E2
Campaign In F2:

This array formula, confirmed with CTRL-SHIFT-ENTER to activate the array, in G2:

=INDEX($C$1:$C$21, MATCH(E2, IF($B$1:$B$21 = F2, $A$1:$A$21), 0))

Please Login or Register  to view this content.

The formula in G2 is =INDEX($C$1:$C$21, MATCH(E2 & F2, INDEX(A1:A21 & B1:B21, 0), 0))

Originally Posted by shg

The formula in G2 is =INDEX($C$1:$C$21, MATCH(E2 & F2, INDEX(A1:A21 & B1:B21, 0), 0))

Lovely! A new trick for the notebook. Thanks for that, shg.

or as nothing is used more than once on same day
=SUMPRODUCT((A2:A21=A2)*(B2:B21=B2)*(C2:C21))
but i must admit its interesting how
INDEX(A1:A21 & B1:B21) shows a1b1:a2b2......... how did someone find that out

Originally Posted by JB

Lovely!

Actually, I believe I picked that up from DLL, JB.

Originally Posted by rk

... but can you walk me through it ...

It's the same formula as JB's, except that the inner INDEX function avoids the need for an array formula, because INDEX in that form always returns an array, and Excel knows that.

It catenates the lookup values, looks for an exact match in the catenation of the first two columns of the table, and returns the value in the corresponding position of the third column. Bring up the Formula Auditing toolbar, and use the Evaluate button to see how it works.

You're welcome. Would you please mark the thread as Solved?