1

I have an Excel 2013 table, called 'student_courses' with columns: stu_id, stu_major, stu_course, and a calculated column named validated_crs.

I am trying to create a formula for the calculated column named validated_crs that evaluates every record.

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.

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.

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.

So far, I have found out how to return the second table column values using =INDIRECT("qualified_courses"&"["&[@[stu_major]]&"]"), 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.

Many thanks,

Lindsay

student_courses table:

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

qualified_courses table:

MULTA      MULTB         MULTE          MULTM
ART1301    HIST1301      BIOL1102       HIST1104
BIOL1322   POLS2210      ENGL2303       EDUC2303
ENGL1440   IS3340        ART3303        EDUC1302
           BIOL2302      BIOL1222       MUSC1303
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Lindsay Patterson
  • 99
  • 7

1 Answers1

2

This formula builds on yours:

=IF(ISNUMBER(MATCH([@[stu_course]],INDIRECT("qualified_courses"&"["&[@[stu_major]]&"]"),0)),"Validated","")

We use the MATCH function to search the returned column for the course. If there is one then it returns a number, otherwise an error.

The ISNUMBER returns TRUE/FALSE which the IF uses to determine which outcome.

But I do not like the INDIRECT() Formula as it is a VOLATILE formula. I prefer the INDEX Formula:

=IF(ISNUMBER(MATCH([@[stu_course]],INDEX(qualified_courses,0,MATCH([@[stu_major]],qualified_courses[#Headers],0)),0)),"Validated","")

Which will return the same.

Instead of the INDIRECT to find the correct column we use INDEX/MATCH. Once again the MATCH returns a number, this time of the column in the second table.

The INDEX() uses this nubmer to return the full column at that position. The 0 at the second criterion states that we want the full column.

Then it is just like the first.

VOLATILE formulas are those that recalculate every time Excel recalculates whether the data to which it is tied changed or not.

Non Volatile formulas only recalculate if the data to which they refer changes.

The validated_cs column in the picture is the top formula and the next column is the bottom:

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thank you Scott. I am not able to get either to work yet. I am using ctrl+shift+enter for the array index match. Would it make a difference that the two tables are on different worksheets in the same workbook? – Lindsay Patterson Jun 11 '16 at 00:21
  • It should not matter if they are on different sheets as the tables are bound to the workbook. Do not use Ctrl-Shift-Enter, neither of these are array formulas, they just look scary. Check your data for spaces and other non printable characters, also check to make sure the column and table names are spelled the same in the formula and in the tables. @LindsayPatterson – Scott Craner Jun 11 '16 at 00:23
  • When I reproduce the mock data and use your index match, it works great, so I've just got some tweaking to do to figure out how to make it work on the real dataset. – Lindsay Patterson Jun 11 '16 at 00:30
  • Got it. Thank you so much Scott :D – Lindsay Patterson Jun 11 '16 at 00:34