title: SQL Server- How can I perform the same function as Excel index/match?
body: <p>So I have the following code which works fine:</p>
  
  <pre><code>SELECT 
      CODE,
      TRADEDATE,
      SETTLE,
      SUBSTRING(EXPIRY, 1, 1) AS CONTRACT_CODE,
      SUBSTRING(EXPIRY, 2, 4) AS CONTRACT_YEAR 
  FROM
      QUANDL.DBO.AR_MWCS$
  INNER JOIN 
      QUANDL.DBO.AR_MWCF$ ON QUANDL.DBO.AR_MWCS$.FULL_code = QUANDL.DBO.AR_MWCF$.FULL_CODE
   WHERE 
      CODE IN ('CI', 'TC', 'PG', 'PW', 'NG', 'NL', 'PD', '8X', 'PH', 'PM', 'NJ', 'NX', 'TZ6', 'TR', 'NW', 'CL', 'CY', 'WJ')
      AND EXPIRY IN ('H2020')
  ORDER BY 
      CODE ASC, TRADEDATE DESC, EXPIRY ASC
  </code></pre>
  
  <p>The output looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/RIbgK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RIbgK.png" alt="enter image description here"></a></p>
  
  <p>I have another table called "contract_key" which contains two columns. One is "contract_code" and the other is "contract_month". What I need is to join the tables so I can see the contract_month, March, instead of the contract_code, H.</p>
  
  <p>In Excel it would be </p>
  
  <pre><code>=INDEX(A:A, MATCH(E2,B:B,0))
  </code></pre>
  
  <p>where column A is the month and column B is the code,cell E2 is the code H and the formula returns "3".</p>
  
  <p>The desired output would look like this instead:</p>
  
  <p><a href="https://i.stack.imgur.com/Bz5JE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Bz5JE.png" alt="enter image description here"></a></p>
  
  <p>I'm using AR_MWCS$ to pull in the settle price. The table looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/MS4f5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/MS4f5.png" alt="enter image description here"></a></p>
  
  <p>Table AR_MWCF$ looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/8ZG91.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8ZG91.png" alt="enter image description here"></a></p>
  
  <p>I'm using substring to split EXPIRY into "H" and "2020"</p>
  
  <p>Table CONTRACT_KEY looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/hnBt2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hnBt2.png" alt="enter image description here"></a></p>
  
  <p>So I need to join the CONTRACT_CODE from table CONTRACT_KEY and the substring CONTRACT_CODE created from table AR_MWCF$ to find the contract month then combine the contract month with the contract year as a date.</p>
  
  <p>I was able to get my desired output by modifying the code to:</p>
  
  <pre><code>SELECT 
       CODE
      ,TRADEDATE
      ,SETTLE
      ,CONCAT(CONTRACT_MONTH,'-','01','-',CONTRACT_YEAR) AS EXPIRATION
  
  FROM
       QUANDL.DBO.AR_MWCS$
  
  INNER JOIN 
      QUANDL.DBO.AR_MWCF$
   ON QUANDL.DBO.AR_MWCS$.FULL_code = QUANDL.DBO.AR_MWCF$.FULL_CODE
  
  INNER JOIN
      QUANDL.DBO.CONTRACT_KEY
  ON QUANDL.DBO.AR_MWCF$.CONTRACT_CODE = QUANDL.DBO.CONTRACT_KEY.CONTRACT_CODE
  
  
  WHERE 
      CODE IN ('CI','TC','PG','PW','NG','NL','PD','8X','PH','PM','NJ','NX','TZ6','TR','NW','CL','CY','WJ')
  
  ORDER BY 
      CODE ASC, TRADEDATE DESC, EXPIRY ASC
  </code></pre>
  
  <p>Which returns the following:</p>
  
  <p><a href="https://i.stack.imgur.com/bzrzZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bzrzZ.png" alt="enter image description here"></a></p>