how to display value of next cell based on a formula

[clhmms]

Below is a sample of data with which I am working. I created a formula that will extract the value of 'A' based on the year selected. (Formula= =VLOOKUP($F28,OFFSET($B$26,MATCH(G$27,$B$27:$B$41,0),1,COUNTIF($B$27:$B$41,G$27),2),2,0)
The problem is that the formula will not work on all rows because the year only displays on group A of the yearly set; the year does not display on rows B through E of the set. I need a formula that will extract and display the value of 'B' through 'E' for the selected year.

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <table class="MsoNormalTable" style="width: 181.8pt; margin-left: 5.4pt; border-collapse: collapse;" width="242" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15.75pt;"> <td colspan="3" style="width: 181.8pt; padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="242" nowrap="nowrap"> Original Pivot Table Data
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border: 1pt solid windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap"> Year
</td> <td style="width: 39.95pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> Group
</td> <td style="width: 109.95pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> Values
</td> </tr> <tr style="height: 15.75pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="43" nowrap="nowrap">

2001​

</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="53" nowrap="nowrap"> A
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="147" nowrap="nowrap"> 250
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> B
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 356
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> C
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 380
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> D
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 326
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> E
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 145
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">

2002​

</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> A
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 90
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> B
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 987
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> C
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 698
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> D
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 458
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> E
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 2,356
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">

2003​

</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> A
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 236
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> B
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 654
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> C
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 587
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> D
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 8,479
</td> </tr> <tr style="height: 15pt;"> <td style="width: 31.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="43" nowrap="nowrap">
</td> <td style="width: 39.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="53" nowrap="nowrap"> E
</td> <td style="width: 109.95pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="147" nowrap="nowrap"> 326
</td> </tr> </tbody></table>
```````````````````````````````````````````
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <table class="MsoNormalTable" style="width: 231pt; margin-left: 5.4pt; border-collapse: collapse;" width="308" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15.75pt;"> <td style="width: 47.5pt; padding: 0in 5.4pt; height: 15.75pt;" width="63"> Results
</td> <td style="width: 106.55pt; padding: 0in 5.4pt; height: 15.75pt;" width="142">
</td> <td style="width: 76.95pt; padding: 0in 5.4pt; height: 15.75pt;" width="103">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 47.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="63" nowrap="nowrap">
</td> <td colspan="2" style="width: 183.5pt; border-width: 1pt medium medium; border-style: solid none none; border-color: windowtext -moz-use-text-color -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" width="245" nowrap="nowrap">

Years & Values​

</td> </tr> <tr style="height: 15.75pt;"> <td style="width: 47.5pt; padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="63" nowrap="nowrap">

Group​

</td> <td style="width: 106.55pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="142" nowrap="nowrap">

2001​

</td> <td style="width: 76.95pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="103" nowrap="nowrap">

2003​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 47.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="63" nowrap="nowrap">

A​

</td> <td style="width: 106.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> 250
</td> <td style="width: 76.95pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103" nowrap="nowrap"> 236
</td> </tr> <tr style="height: 15pt;"> <td style="width: 47.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="63" nowrap="nowrap">

B​

</td> <td style="width: 106.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> ?
</td> <td style="width: 76.95pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103" nowrap="nowrap"> ?
</td> </tr> <tr style="height: 15pt;"> <td style="width: 47.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="63" nowrap="nowrap">

C​

</td> <td style="width: 106.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> ?
</td> <td style="width: 76.95pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103" nowrap="nowrap"> ?
</td> </tr> <tr style="height: 15pt;"> <td style="width: 47.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="63" nowrap="nowrap">

D​

</td> <td style="width: 106.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> ?
</td> <td style="width: 76.95pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103" nowrap="nowrap"> ?
</td> </tr> <tr style="height: 15pt;"> <td style="width: 47.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="63" nowrap="nowrap">

E​

</td> <td style="width: 106.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> ?
</td> <td style="width: 76.95pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103" nowrap="nowrap"> ?
</td> </tr> <tr style="height: 15pt;"> <td style="width: 47.5pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="63" nowrap="nowrap">
</td> <td style="width: 106.55pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap">
</td> <td style="width: 76.95pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103" nowrap="nowrap">
</td> </tr> </tbody></table> I have tried various match formulas and nothing has worked. I have tried to find a formula that will display the value of the group based on above selected year. I am out of ideas. Does anyone have an idea?

Thank you in advance for your input!

 

[Andrew Poulsom]

Can't you use the GETPIVOTDATA function?

http://www.contextures.com/xlPivot06.html

 

[clhmms]

No, I do not think so. I cannot work directly in the pivot table. I need to extract data from the pivot table and place it into a new table. The user will select a year and the values need to populate the table.

Can't you use the GETPIVOTDATA function?

http://www.contextures.com/xlPivot06.html

 

[Andrew Poulsom]

The GETPIVOTDATA function does extract data from a pivot table.

 

[clhmms]

Technically I was working with a copy of the pivot table data - not the actual pivot table.

 

[Matty]

If your data is a copy of a PT, the simplest way to do this would be to populate the empty Cells with the relevant year and we can then use a simple INDEX and MATCH combination.

To populate the empty Cells, do as follows:

Select from the the first empty Cell to the last empty Cell, hit F5 > Special > Blanks > OK. Now, hit = and select the first non-empty Cell, and while holding down CTRL, hit ENTER. This should fill the empty Cells with the value appearing in the first non-empty Cell preceding it. You can now select all of those Cells and choose Copy > Paste Special > Values.

Now, assuming your first table resides in A1:C16, try this for Group A for 2001:

=INDEX($C$2:$C$16,MATCH(1,IF($A$2:$A$16=B$21,IF($B$2:$B$16=$A22,1)),0))

The formula is array in nature so needs committing with CTRL+SHIFT+ENTER. You can now drag it down and across.

Hope this helps.

Matty

 

[clhmms]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi Matty. Is there a way that excel can read the address of the cell instead? For example, the formula in cell B23 returned the value of 250, which is from cell C2. Is it possible to create a formula that will copy the values of the four consecutive cells located beneath C2 respectively in the cells beneath B23.

<table class="MsoNormalTable" style="width: 194.8pt; margin-left: 5.4pt; border-collapse: collapse;" width="260" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> A
</td> <td style="width: 48.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> B
</td> <td style="width: 48.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> C
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

1​

</td> <td style="width: 48.8pt; border: 1pt solid windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> Year
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> Group
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> Values
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

2​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

2001​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> A
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 250
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

3​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> B
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 356
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

4​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> C
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 380
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

5​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> D
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 326
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

6​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> E
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 145
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

7​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

2002​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> A
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 90
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

8​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> B
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 987
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

9​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> C
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 698
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

10​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> D
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 458
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

11​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> E
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 2,356
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

12​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

2003​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> A
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 236
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

13​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> B
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 654
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

14​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> C
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 587
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

15​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> D
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 8,479
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

16​

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> E
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 326
</td> </tr> </tbody></table>
<table class="MsoNormalTable" style="width: 284.55pt; margin-left: 5.4pt; border-collapse: collapse;" width="379" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

​

</td> <td style="width: 51.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="69" nowrap="nowrap"> A
</td> <td style="width: 106.55pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> B
</td> <td style="width: 77.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="104" nowrap="nowrap"> C
</td> </tr> <tr style="height: 15.75pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="65" nowrap="nowrap">

20​

</td> <td style="width: 51.8pt; padding: 0in 5.4pt; height: 15.75pt;" width="69"> Results
</td> <td style="width: 106.55pt; padding: 0in 5.4pt; height: 15.75pt;" width="142">
</td> <td style="width: 77.8pt; padding: 0in 5.4pt; height: 15.75pt;" width="104">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

21​

</td> <td style="width: 51.8pt; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="69" nowrap="nowrap">
</td> <td colspan="2" style="width: 184.35pt; border-width: 1pt medium medium; border-style: solid none none; border-color: windowtext -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" width="246" nowrap="nowrap">

Years & Values​

</td> </tr> <tr style="height: 15.75pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="65" nowrap="nowrap">

22​

</td> <td style="width: 51.8pt; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="69" nowrap="nowrap">

Group​

</td> <td style="width: 106.55pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="142" nowrap="nowrap">

2001​

</td> <td style="width: 77.8pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="104" nowrap="nowrap">

2003​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

23​

</td> <td style="width: 51.8pt; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="69" nowrap="nowrap">

A​

</td> <td style="width: 106.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> 250
</td> <td style="width: 77.8pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="104" nowrap="nowrap"> 236
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

24​

</td> <td style="width: 51.8pt; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="69" nowrap="nowrap">

B​

</td> <td style="width: 106.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> ?
</td> <td style="width: 77.8pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="104" nowrap="nowrap"> ?
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

25​

</td> <td style="width: 51.8pt; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="69" nowrap="nowrap">

C​

</td> <td style="width: 106.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> ?
</td> <td style="width: 77.8pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="104" nowrap="nowrap"> ?
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

26​

</td> <td style="width: 51.8pt; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="69" nowrap="nowrap">

D​

</td> <td style="width: 106.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> ?
</td> <td style="width: 77.8pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="104" nowrap="nowrap"> ?
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

27​

</td> <td style="width: 51.8pt; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="69" nowrap="nowrap">

E​

</td> <td style="width: 106.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> ?
</td> <td style="width: 77.8pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="104" nowrap="nowrap"> ?
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.4pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

28​

</td> <td style="width: 51.8pt; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="69" nowrap="nowrap">
</td> <td style="width: 106.55pt; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap"> 250
</td> <td style="width: 77.8pt; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="104" nowrap="nowrap"> 236
</td> </tr> </tbody></table>
Thank you very much for helping!

 

[scottylad2]

If your free to add to and manipulate your data, then this is quite a neat alternative to an array. I've created some named ranges from within your data range,
A2:A6 named TwoThOne
A7:A11 TwoThTwo
A12:A16 TwoThThree

and also created another small table from E8:F10 this table is a Vlookup of your lookup years, 2001 2002 2003 in the first column and the corresponding name TwoThOne etc then using the indirect function inside another Vlookup you can draw the appropriate values for the year you choose. I have a dropdown en E2 and on selecting a year i can pull through the data for that year.

Excel Workbook
	A	B	C	D	E	F	G
1	Year	Group	Values		Year	Group	Values
2	2001	A	250		2003	A	236
3		B	356			B	654
4		C	380			C	587
5		D	326			D	8479
6		E	145			E	326
7	2002	A	90
8		B	987		2001	TwoThOne
9		C	698		2002	TwoThTwo
10		D	458		2003	TwoThThree
11		E	2,356
12	2003	A	236
13		B	654
14		C	587
15		D	8,479
16		E	326
Sheet2

Excel Workbook
	E	F	G
1	Year	Group	Values
2	2001	A	250
3		B	356
4		C	380
5		D	326
6		E	145
Sheet2

Excel Workbook
	E	F	G
1	Year	Group	Values
2	2002	A	90
3		B	987
4		C	698
5		D	458
6		E	2356
Sheet2

obviously you can store the data tables on a seperate sheet. Quite neat if the ranges of data your looking up will structually stay the same

 

[clhmms]

Wow thank you for all of your effort. It will take a bit of time to see if it works - but as of now I think it does. Thank you again!