Auto Lookup and Populate
 

Name Jan-2008 Feb-2008 Mar-2008 Apr-2008 May-2008 Jun-2008
Big Blue X


Above is an example of the spread sheet I am trying to populate. What I need
to do is match two variables name and date (In this example Name: Big Blue,
Date: Jan-2008) in the above sheet with name and date in the sheet below and
return a variable into the cell marked X. Below is what the source data is
formatted like.

1 2 3 4 5 6
Big Blue Jan-2008 May-2008
Small Green Jul-2008
Medium Yellow Feb-2008

Once the name and date have been matched it would return "1" from the data
sheet. The issue I am running into is that I must perform this check for
every date in Big Blues time line and then return the value if there is a
match.

Currently this is the equation I am using:

=IF(AND(VLOOKUP($A$1,Sheet2!$A$7:$H$13,4,0)=E3,(V LOOKUP($A$1,Sheet2!$A$7:$H$13,4,0)<F3)),Sheet2!$D$ 6&",","")&IF(AND(VLOOKUP($A$1,Sheet2!$A$7:$H$13,5, 0)=E3,(VLOOKUP($A$1,Sheet2!$A$7:$H$13,5,0)<F3)),S heet2!$E$6&",","")&IF(AND(VLOOKUP($A$1,Sheet2!$A$7 :$H$13,6,0)=E3,(VLOOKUP($A$1,Sheet2!$A$7:$H$13,6, 0)<F3)),Sheet2!$F$6&",","")

***($A$1,Sheet2!$A$7:$H$13,4,0)=E3,(VLOOKUP($A$1, Sheet2!$A$7:$H$13,4,0)<F3)),
This portion is used to check dates since it can be the 15th of the month or
1st***

As you can see if only checks three columns so far. The downfall is that it
checks each column one at a time and there is a potential for there to be
over 100 columns that must be checked. I would prefer to do this without a
macro, anyone have an idea?