title: Unable to use SUBSTITUTE(ADDRESS(MATCH))) combination as a range in a LOOKUP function in Excel
body: <p>I have an Excel sheet containing a matrix of [Year &amp; Week number] in rows and [name of Employees] in row1 giving values of available weekly hours.</p>
  
  <p><img src="https://i.stack.imgur.com/9GJWk.jpg" alt="Excel_Query_Data"></p>
  
  <p>I am able to successfully use the Excel formula </p>
  
  <pre><code>=LOOKUP(2,1/((A:A=2018)*(B:B=31)),D:D)
  </code></pre>
  
  <p>at cell F2 for a reverse lookup. It gives the result <strong>15</strong> correctly.</p>
  
  <p>However, I wanted to replace range D:D in the above formula to a dynamic range  by identifying the column when the employee is known. </p>
  
  <p>So I tried replacing by the formula</p>
  
  <pre><code>SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","") &amp;":"&amp;SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","")
  </code></pre>
  
  <p>This portion of the formula works and gives <strong>D:D</strong> for Employee2. This is shown to work in cell F4.</p>
  
  <p>But the revised formula gives an error of <strong>#Value!</strong> at cell F6.</p>
  
  <p>It says "A value used in the formula is of the wrong data type."
  The revised formula which does not work is:</p>
  
  <pre><code>=LOOKUP(2,1/((A:A=2018)*(B:B=31)),SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","") &amp;":"&amp;SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1",""))
  </code></pre>
  
  <p>I hope someone can help show me where I am making an error in trying to replace range D:D in the LOOKUP formula with the combination of SUBSTITUTE, ADDRESS &amp; MATCH functions.</p>
  
  <p>Thanks to all trying to help in advance.</p>