title: How to do a range index and match or vlookup in power pivot excel?
body: <p>These are the individual data points of the <code>data model</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/5KU7w.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5KU7w.png" alt="enter image description here"></a></p>
  
  <p>These are the monthly salaries of the employees (obtained using the pivot table from the data model's data):</p>
  
  <p><a href="https://i.stack.imgur.com/ecmee.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ecmee.png" alt="enter image description here"></a></p>
  
  <p>Each cell will then be used as the Lookup value which will be run through a table.</p>
  
  <p>The lookup value is to be looked up in <code>column A</code> and <code>column B</code> of the table below and if it is matched (within the range), it will return the corresponding value under <code>column C</code>.</p>
  
  <p><a href="https://i.stack.imgur.com/Xla9M.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Xla9M.png" alt="enter image description here"></a></p>
  
  <p>I am unable to find any <code>index and match</code> or <code>vlookup</code> functions in the power pivot functionality of excel using <code>measures</code>--which are used to get some analytics on aggregated values on report objects such as pivot tables.</p>
  
  <p>I have found <code>LOOKUPVALUE( &lt;result_columnName&gt;, &lt;search_columnName&gt;, &lt;search_value&gt;[, &lt;search_columnName&gt;, &lt;search_value&gt;]…)</code> which is a <code>DAX</code> function however, the issue here is that I am doing a <strong>range lookup</strong> and as shown below, I don't know if you can have an array as an argument to the function.
  <a href="https://i.stack.imgur.com/HPBRo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HPBRo.png" alt="enter image description here"></a></p>
  
  <p>Traditional <code>calculated fields</code> also do not allow arrays in the formulas.</p>
  
  <p><code>Lookupvalue()</code> only works on a single column lookups because it will return an empty cell if it cannot find a match as shown below:</p>
  
  <p><a href="https://i.stack.imgur.com/0EyNH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0EyNH.png" alt="enter image description here"></a></p>
  
  <p>But when it does find a match using the table below:</p>
  
  <p><a href="https://i.stack.imgur.com/Ygcap.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ygcap.png" alt="enter image description here"></a></p>
  
  <p>It will work just fine:</p>
  
  <p><a href="https://i.stack.imgur.com/dKEUp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dKEUp.png" alt="enter image description here"></a></p>