Multiple Conditions to return a single value _help me

[Doogs2009]

Ok, i have searched every possible Excel help tutorial and analysed every reply to problems regarding how to find a single value using multiple lookup criteria. I have explored SUMPRODUCT, VLOOKUP + MATCH, and LOOKUP and i have been doing this for weeks, but still—I cannot find a suitable Excel function, formula to solve my problem and get me the desired result. So I am hoping someone out there in advanced excel land can help me.

I have a huge data table, separated into sections. All the sections are the same dimensions, that is, they are all the same row length and column width. The tabled data sections are layed out exactly as follows:-

<TABLE style="WIDTH: 309pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=410 border=0 x:str><COLGROUP><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7387" width=202><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 152pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #333333" width=202 height=18>Sequence #'s Issued</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff" width=94 x:num="39447">Dec-07</TD><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff" width=114 x:num="39478">Jan-08</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 152pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=202 height=20>Employee 1</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="66">66 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="276">276 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 152pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=202 height=20>Employee 2</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="162">162 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="572">572 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 152pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=202 height=20>Employee 3</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="55">55 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="107">107 </TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 309pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=410 border=0 x:str><COLGROUP><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7387" width=202><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 152pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #333333" width=202 height=18>Variation $ Dollars</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff" align=right width=94 x:num="39447">Dec-07</TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #3366ff" align=right width=114 x:num="39478">Jan-08</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl77 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 152pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=202 height=20>Employe 1</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="3408.31"> $ 3,408 </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="163851.91"> $ 163,852 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl77 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 152pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=202 height=20>Employee 2</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="129559.11"> $ 129,559 </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="333003.22"> $ 333,003 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl77 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 152pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=202 height=20>Employee 3</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="4946.31"> $ 4,946 </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="20711.66"> $ 20,712 </TD></TR></TBODY></TABLE>

As you can see above, each section has name ( we will call this a "measure), for example, the first section is called, "Sequence #'s Issued", and so on and so forth. The first column of each table section is an Employees name, and the columns to the right of these are monthly periods starting at Dec 07 ending at current month across the page. I have just given you the first 2 months of data for now. Hope you are still with me......

In an associated Dashboard I am trying to write some type of Lookup formula that will return the value in a given month for a given employee, for a given measure. Ie. There are 3 conditions that have to be met in order to return the correct value. So does anyone know any VBA code or Excel function (Excel 2003) that will return the value in Jan 08 for
Employee 1 under the Sequence #'s Issued table, the result will be: 276.

Now, I have never used INDEX function because I am very scared of this one, so if that is a possible solution then could someone please give it to me in as plain as English as possible.

Just a further piece of information that may help in trying to solve this, in my dashboard I have created Drop Down lists for "Employee Names", and also for "Measures", The way i want the formula to work is that whenever one of these items are selected from the dropdown lists it will automatically detect what is now in the cell, refer back to the main data tables and pull me the value I need.

I thank anyone in advance who can help me with this excrutating task.

Many thanks

 

[barry houdini]

Hello Doogs2009, welcome to MrExcel

Assuming that there will be continuous names under each "measure" and then at least one blank line before the next "measure"....

Assuming also that weeks are in B1:Z1 and will then be identical in other rows where they are shown

Measures and Employees in A2:A1000

in AA2 a specific measure
in AA3 a specific week
in AA4 a specific employee

use this formula in AA5 to find the row(within range) of the measure

=MATCH(AA2,A2:A1000,0)

and then in AA6 the last row with

=MATCH(TRUE,INDEX(INDEX(A2:A1000,AA5):A1000="",0),0)+AA5

then in AA7 to get the required result

=INDEX(B2:Z1000,MATCH(AA4,INDEX(A2:A1000,AA5):INDEX(A2:A1000,AA6),0)+AA5-1,MATCH(AA3,B1:Z1,0))

If you want you could eliminate cells AA5 and AA6 and put everything together in one formula, i.e.

=INDEX(B2:Z1000,MATCH(AA4,INDEX(A2:A1000,MATCH(AA2,A2:A1000,0)):INDEX(A2:A1000,MATCH(TRUE,INDEX(INDEX(A2:A1000,MATCH(AA2,A2:A1000,0)):A1000="",0),0)+MATCH(AA2,A2:A1000,0)),0)+MATCH(AA2,A2:A1000,0)-1,MATCH(AA3,B1:Z1,0))

 

[tfaulkes]

Doog -

Well your three conditions are: Employee/Measurement/Date, correct? I see that you have a list for Names and Measures, but are you making the user select the date?

You can name the sequence # table as Table1, and the Variation as Table2. then you can use a vlookup function:

=VLOOKUP(EMPL_NAME,IF(LEFT(MEASURE_TABLES,3)="Seq",tab1,tab2),MATCH(USER_DATE,7:7,0))

the left function just tests what "measurement" the user selected to pull from the correct table....hope this helps

 

[Doogs2009]

Thanks TFAULKES for suggesting a Vlookup function. However, what I did not mention was that I cannot use a Vlookup formula at the moment. You see, the data tables that contain the information for each of the "measures" although they contain the same list of employee names, and the same Monthly periods across the top cannot be sorted alphabetically because the name of the table is in the top left hand corner. To use Vlookup, the array must be sorted in ascending doesnt it?

Thanks

 

[Doogs2009]

Hi TFAULKES

Something else i forgot to mention. The date is not selected by the User. It is an automated field within my Dashboard, but the cell still needs to be referenced as part of my function because the 3 conditions I have to refer to are:-

Employee Name
Measure
Month

Hope this helps further.
Thanks

 

[Doogs2009]

Hi Barry

Thanks for this I will give it a go. Just wondering if you could explain to me as simple language as possible, what exactly does the INDEX function do, what does it tell us and do you find it very useful?
Thanks

 

[Doogs2009]

Hi Barry

Thanks for this I will give it a go. Just wondering if you could explain to me as simple language as possible, what exactly does the INDEX function do, what does it tell us and do you find it very useful?
Thanks

 

[Doogs2009]

Hi Barry Houdini

I tried your first function, =MATCH(AA2,A2:A1000,0), and this in itself actually worked. It returned 28 which is the row number in the main data table where the Measure is located.

Then I tried the next function, =MATCH(TRUE,INDEX(INDEX(A2:A1000,AA5):A1000="",0),0)+AA5

in which I substituted my own ranges and cell references into your formula, and I got a # Value error. So i did not continue with your next function. Probably best to break down the function into small parts.

I am now showing you the Dashboard where i need the values to populate to:-

<TABLE style="WIDTH: 423pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=564 border=0 x:str><COLGROUP><COL style="WIDTH: 147pt; mso-width-source: userset; mso-width-alt: 7168" width=196><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 9pt; mso-width-source: userset; mso-width-alt: 438" width=12><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD class=xl77 id=td_post_2126647 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 147pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 18.75pt; BACKGROUND-COLOR: navy" width=196 height=25>Individual</TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 118pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=157></TD><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=116></TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 9pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=12></TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=83></TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl93 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 18.75pt; BACKGROUND-COLOR: #339966" height=25>Select Consultant</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">Select Consultant</TD><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl93 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 18.75pt; BACKGROUND-COLOR: #339966" height=25>Select Measure</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white">Measure</TD><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25></TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl80 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40147">Nov-09</TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl80 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40117" x:fmla="=DATE(YEAR(C4),MONTH(C4)-0,0)">Oct-09</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD class=xl78 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white"> </TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Sequence #'s Issued</TD><TD class=xl87 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD><TD class=xl88 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl94 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ff6600" height=17>Compared to Group</TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ff6600" height=18> </TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl83 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:str="Variation $ Dollars"> Variation $ Dollars </TD><TD class=xl84 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl83 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl94 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ff6600" height=17>Compared to Group</TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD></TR><TR style="HEIGHT: 6.75pt; mso-height-source: userset" height=9><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 6.75pt; BACKGROUND-COLOR: #ff6600" height=9> </TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl83 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:str="Rejected $ Dollars"> Rejected $ Dollars </TD><TD class=xl84 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl83 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl94 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ff6600" height=17>Compared to Group</TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD></TR><TR style="HEIGHT: 6.75pt; mso-height-source: userset" height=9><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 6.75pt; BACKGROUND-COLOR: #ff6600" height=9> </TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:str="Phone Requests"> Phone Requests </TD><TD class=xl90 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl91 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD><TD class=xl92 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl91 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl94 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ff6600" height=17>Compared to Group</TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD></TR><TR style="HEIGHT: 6.75pt; mso-height-source: userset" height=9><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 6.75pt; BACKGROUND-COLOR: #ff6600" height=9> </TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD></TR><TR style="HEIGHT: 6.75pt; mso-height-source: userset" height=9><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 6.75pt; BACKGROUND-COLOR: #ff6600" height=9> </TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:str="Other Requests"> Other Requests </TD><TD class=xl90 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl91 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD><TD class=xl92 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl91 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl94 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ff6600" height=17>Compared to Group</TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ff6600"> </TD></TR></TBODY></TABLE>


Basically here is what supposed to happen:-
1. The user selects a Consultant name (employee)
2. The user then selects a Measure.

Both selections are from a drop down list.

As I said earlier, the months, Nov 09 and Oct 09 etc, are automatically there already. The month needs to be factored in with the formula however so it also needs to lookup that month in the main data table.

I need the appropriate single values to be returned from the main data table into the cells above Under each month column, alongside the respective measure, for example what is the value of Sequence #'s Issued for Nov 09, the result will be depended on what the user selects from the drop down lists in the Select Consultant box, and the Select Measure box.

Hope you can shed some light on this for me please.
Many thanks in advance.

Craig

 

[T. Valko]

All the sections are the same dimensions, that is, they are all the same row length and column width.

As long as each group has the exact same sequence of employees as is shown in your sample data...

Let's assume...

Data in the range A2:C10

Dates in B2:C2 are true Excel dates and those dates are the 1st of the month and are just formatted to display as mmm-yy (Man, I hate ambiguious dates! )

A15 = drop down list of the groups: Sequence #'s Issued, Variation $ Dollars
B15 = drop down list of employees
C15 = lookup date = 12/1/2007, 1/1/2008

=INDEX(B3:C10,MATCH(A15,A2:A10,0)+MATCH(B15,A3:A5,0)-1,MATCH(C15,B2:C2,0))