VLOOKUP that returns a Formula instead of value

Wiseforg

New Member
Joined
Feb 6, 2017
Messages
2
Hi there,

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:

HTML:
<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 />

Formula for UID column: {=INDEX(Table2[UID],MATCH(Table1[[#This Row],[Role]],IF(Table2[Complexity]=Table1[[#This Row],[Complexity]],Table2[Role]),0))}

Formula for Formula column: =VLOOKUP(Table1[[#This Row],[UID_Formula]],Table2[[UID]:
],2,FALSE) My Table2 has the referenced Formula [HTML]<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 />[/HTML]
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,460
Office Version
365, 2010
Platform
Windows, Mobile
I would suggest not putting your tables between HTML tags :biggrin:

<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>


<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><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>
 

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top