VLOOKUP that returns a Formula instead of value

[Wiseforg]

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:

[RANGE=cls:xl2bb-100][XR][XH=cs:9]Excel 2007[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][/XR][XR][XH]1[/XH][XD=h:l]User[/XD][XD=h:l]Criteria1[/XD][XD=h:l]Criteria2[/XD][XD=h:l]Criteria3[/XD][XD=h:l]Complexity[/XD][XD=h:l]Role[/XD][XD=h:l]UID_Formula[/XD][XD=h:l]Formula[/XD][/XR][XR][XH]2[/XH][XD=h:l]Joe[/XD][XD=h:l]yes[/XD][XD=h:l]yes[/XD][XD=h:l]no[/XD][XD=h:l]High[/XD][XD=h:l]Manager[/XD][XD=h:r]1[/XD][XD=h:r]#VALUE![/XD][/XR][XR][XH]3[/XH][XD=h:l]Jack[/XD][XD=h:l]no[/XD][XD=h:l]yes[/XD][XD=h:l]yes[/XD][XD=h:l]Low[/XD][XD=h:l]User[/XD][XD=h:r]4[/XD][XD=h:r]#VALUE![/XD][/XR][XR][XH]4[/XH][XD=h:l]Tim[/XD][XD=h:l]yes[/XD][XD=h:l]yes[/XD][XD=h:l]yes[/XD][XD=h:l]Low[/XD][XD=h:l]Manager[/XD][XD=h:r]2[/XD][XD=h:r]#VALUE![/XD][/XR][XR][XH]5[/XH][XD=h:l]Bob[/XD][XD=h:l]no[/XD][XD=h:l]no[/XD][XD=h:l]no[/XD][XD=h:l]High[/XD][XD=h:l]User[/XD][XD=h:r]3[/XD][XD=h:r]#VALUE![/XD][/XR][XR][XH]6[/XH][XD=h:l]Nick[/XD][XD=h:l]yes[/XD][XD=h:l]no[/XD][XD=h:l]yes[/XD][XD=h:l]Low[/XD][XD=h:l]User[/XD][XD=h:r]4[/XD][XD=h:r]#VALUE![/XD][/XR][XR][XH]7[/XH][XD=h:l]Helen[/XD][XD=h:l]no[/XD][XD=h:l]yes[/XD][XD=h:l]no[/XD][XD=h:l]High[/XD][XD=h:l]Manager[/XD][XD=h:r]1[/XD][XD=h:r]#VALUE![/XD][/XR][XR][XH=cs:9][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

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] [RANGE=cls:xl2bb-100][XR][XH=cs:5]Excel 2007[/XH][/XR][XR][XH][/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][/XR][XR][XH]10[/XH][XD=h:l]Role[/XD][XD=h:l]Complexity[/XD][XD=h:l]UID[/XD][XD=h:l]Formula[/XD][/XR][XR][XH]11[/XH][XD=h:l]Manager[/XD][XD=h:l]High[/XD][XD=h:r]1[/XD][XD=h:r|cls:fx][FORMULA==SUMPRODUCT((Table1[[#This Row],[Criteria1]]<>"yes")+(Table1[[#This Row],[Criteria3]]<>"yes")+(Table1[[#This Row],[Criteria2]]<>"yes"))/3]#VALUE![/XD][/XR][XR][XH]12[/XH][XD=h:l]Manager[/XD][XD=h:l]Low[/XD][XD=h:r]2[/XD][XD=h:r|cls:fx]#VALUE![/XD][/XR][XR][XH]13[/XH][XD=h:l]User[/XD][XD=h:l]High[/XD][XD=h:r]3[/XD][XD=h:r|cls:fx]#VALUE![/XD][/XR][XR][XH]14[/XH][XD=h:l]User[/XD][XD=h:l]Low[/XD][XD=h:r]4[/XD][XD=h:r|cls:fx]#VALUE![/XD][/XR][XR][XH=cs:5]

#VALUE!

[/XH][/XR][/RANGE]

Cell Formulas
Range	Formula
E11	=SUMPRODUCT((Table1[[#This Row],[Criteria1]]<>"yes")+(Table1[[#This Row],[Criteria3]]<>"yes")+(Table1[[#This Row],[Criteria2]]<>"yes"))/3
E12	=SUMPRODUCT((Table1[[#This Row],[Criteria1]]<>"yes")+(Table1[[#This Row],[Criteria3]]<>"yes"))/2
E13	=SUMPRODUCT((Table1[[#This Row],[Criteria3]]<>"yes")+(Table1[[#This Row],[Criteria2]]<>"yes"))/2
E14	=SUMPRODUCT((Table1[[#This Row],[Criteria1]]<>"yes")+(Table1[[#This Row],[Criteria2]]<>"yes"))/2

[/HTML]

 

[MARK858]

I would suggest not putting your tables between HTML tags

Excel 2007
	A	B	C	D	E	F	G	H
1	User	Criteria1	Criteria2	Criteria3	Complexity	Role	UID_Formula	Formula
2	Joe	yes	yes	no	High	Manager	1	#VALUE!
3	Jack	no	yes	yes	Low	User	4	#VALUE!
4	Tim	yes	yes	yes	Low	Manager	2	#VALUE!
5	Bob	no	no	no	High	User	3	#VALUE!
6	Nick	yes	no	yes	Low	User	4	#VALUE!
7	Helen	no	yes	no	High	Manager	1	#VALUE!
Sheet1

Excel 2007
	B	C	D	E
10	Role	Complexity	UID	Formula
11	Manager	High	1	#VALUE!
12	Manager	Low	2	#VALUE!
13	User	High	3	#VALUE!
14	User	Low	4	#VALUE!
Sheet1

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

 

[Wiseforg]

yes, sorry, first time on the forum. Thanks for cleaning it up