title: How can I use structured references to identify column and use row values in the identified column as criteria?
body: <p>I have an Excel 2013 table, called 'student_courses' with columns: stu_id, stu_major, stu_course, and a calculated column named validated_crs.  </p>
  
  <p>I am trying to create a formula for the calculated column named validated_crs that evaluates every record.</p>
  
  <p>In this calculated column, I need to reference the value in the stu_major column to find a column in a second table, 'qualified_courses'.  The headers in the 'qualified_courses' table correlate with the stu_major values.  </p>
  
  <p>I need to check to see if any of the values in the correlated column in 'qualified_courses' match the 'stu_course' value in the record being evaluated in the 'student_courses' table.</p>
  
  <p>If any of the values in the correlated column match the stu_course value for any given record in the first table, then it should return a value of "validated" in the calculated column.</p>
  
  <p>So far, I have found out how to return the second table column values using <code>=INDIRECT("qualified_courses"&amp;"["&amp;[@[stu_major]]&amp;"]")</code>, but I'm not sure how to use the returned set of values to check against the stu_course value to see if there is a match.</p>
  
  <p>Many thanks,</p>
  
  <p>Lindsay</p>
  
  <p><strong>student_courses table:</strong></p>
  
  <pre><code>stu_id     stu_major     stu_course     validated_crs
  5432       MULTE         BIOL1102       validated
  5432       MULTE         MUSC1303       NULL
  5432       MULTE         ENGL2303       validated
  6737       MULTM         HIST1104       validated
  6737       MULTM         BIOL1222       NULL
  6737       MULTM         EDUC2303       validated
  6737       MULTM         EDUC1302       validated
  </code></pre>
  
  <p><strong>qualified_courses table:</strong></p>
  
  <pre><code>MULTA      MULTB         MULTE          MULTM
  ART1301    HIST1301      BIOL1102       HIST1104
  BIOL1322   POLS2210      ENGL2303       EDUC2303
  ENGL1440   IS3340        ART3303        EDUC1302
             BIOL2302      BIOL1222       MUSC1303
  </code></pre>