Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup and Populate | Excel Worksheet Functions | |||
Auto-populate, Auto-copy or Auto-fill? | Excel Worksheet Functions | |||
Auto populate information | Excel Discussion (Misc queries) | |||
Auto populate with value | Excel Discussion (Misc queries) | |||
auto populate | Excel Discussion (Misc queries) |