I am trying to do a VLOOKUP in my Table that will return a formula instead of the value. Or I am very open to any other recommendation that could resolve my problem.
Here is what I have. Consider that I am using Tables as my actual tables are much bigger than what I have here and lines will be added to it monthly.
I have my Table1 which has all of the user information and Criteria that are given to me. My Table2 contains the Formal that should be used depending on the Role and Complexity the user needs to be evaluated on.
I managed to create a UID_Formula column in my Table1 hoping it would simplify my lookup but still nothing.
First Table is like this:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">User</td><td style=";">Criteria1</td><td style=";">Criteria2</td><td style=";">Criteria3</td><td style=";">Complexity</td><td style=";">Role</td><td style=";">UID_Formula</td><td style=";">Formula</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Joe</td><td style=";">yes</td><td style=";">yes</td><td style=";">no</td><td style=";">High</td><td style=";">Manager</td><td style="text-align: right;;">1</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Jack</td><td style=";">no</td><td style=";">yes</td><td style=";">yes</td><td style=";">Low</td><td style=";">User</td><td style="text-align: right;;">4</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Tim</td><td style=";">yes</td><td style=";">yes</td><td style=";">yes</td><td style=";">Low</td><td style=";">Manager</td><td style="text-align: right;;">2</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Bob</td><td style=";">no</td><td style=";">no</td><td style=";">no</td><td style=";">High</td><td style=";">User</td><td style="text-align: right;;">3</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Nick</td><td style=";">yes</td><td style=";">no</td><td style=";">yes</td><td style=";">Low</td><td style=";">User</td><td style="text-align: right;;">4</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Helen</td><td style=";">no</td><td style=";">yes</td><td style=";">no</td><td style=";">High</td><td style=";">Manager</td><td style="text-align: right;;">1</td><td style="text-align: right;;">#VALUE!</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />My Formula for UID column: {=INDEX(Table2[UID],MATCH(Table1[[#This Row],[Role]],IF(Table2[Complexity]=Table1[[#This Row],[Complexity]],Table2[Role]),0))}
My Formula for the Formula column is: =VLOOKUP(Table1[[#This Row],[UID_Formula]],Table2[[UID]:[Formula]],2,FALSE)
Here is my Table2:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Role</td><td style=";">Complexity</td><td style=";">UID</td><td style=";">Formula</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Manager</td><td style=";">High</td><td style="text-align: right;;">1</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Manager</td><td style=";">Low</td><td style="text-align: right;;">2</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">User</td><td style=";">High</td><td style="text-align: right;;">3</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">User</td><td style=";">Low</td><td style="text-align: right;;">4</td><td style="text-align: right;;">#VALUE!</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E11</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Table1[[#This Row],[Criteria1]]<>"yes"</font>)+(<font color="Red">Table1[[#This Row],[Criteria3]]<>"yes"</font>)+(<font color="Red">Table1[[#This Row],[Criteria2]]<>"yes"</font>)</font>)/3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E12</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Table1[[#This Row],[Criteria1]]<>"yes"</font>)+(<font color="Red">Table1[[#This Row],[Criteria3]]<>"yes"</font>)</font>)/2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E13</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Table1[[#This Row],[Criteria3]]<>"yes"</font>)+(<font color="Red">Table1[[#This Row],[Criteria2]]<>"yes"</font>)</font>)/2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E14</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Table1[[#This Row],[Criteria1]]<>"yes"</font>)+(<font color="Red">Table1[[#This Row],[Criteria2]]<>"yes"</font>)</font>)/2</td></tr></tbody></table></td></tr></table><br />Can anyone help?
