Lookups with Case

Exactly how did you relate your tables. Are the two exam (entry?) levels stored in separate tables? (I might put both in the same table.)

There are several ways to get what you want but the option that will work for you depends on the relationships and tables you've set up.

I can't follow from that post exactly how you have linked your tables in relationships. If you can list them like this:

TableName::FieldName = RelatedTableName::FieldName

It would help others understand your set up.

New users often organize data into separate fields when they could, instead enter data into a unified table and then use relationships, finds, sorts and portal filters to control which sub set of the total data is displayed and/or accessible for editing. The unified table is more flexible, overall as you can simply change what data is entered in your table if you find you need to add or remove subjects/levels where in your current approach, you'd have to modify the structure of your database by adding or removing tables--as well as making changes in the layouts that refer to them.

Granted, this is not an iron clad rule and I don't really know what you mean by "each exam has a different set of grade boundaries", though that sounds like something that can be handled fairly easily in a unified table.

Don't see why you can't boil a lot of this down into two tables, One for your Grade boundaries and one for your student exam scores. A third table would, of course, list all the students.

Your ExamScores table would then include the necessary date, subject and level information in additional fields so that you can properly link this record to the appropriate Exam Boundaries record.

I've been re-reading your original post and trying to guess the context of this statement: "my problem is the lack of "if this value is not found use the lower value" as seen in a normal lookup field."

With a field set to use a looked up value setting to copy values from a related table, you have the option to copy the next higher value or the next lower value as standard FileMaker features.

There are also ways to match related records with inequalities so that it can match against a range of values in the related table.

What I'm getting at, is that I don't see the need at all for your Case Function calculation if you structure your data and relationships correctly. I think you can set this up with inequlities to match a range of values to a given related record to avoid the need to enter in "all possible scores".

Say you have this table, using classic percentages for "Letter Grades" just to demo the concept:

Lower Boundary    Upper Boundary      Letter Grade
90                          100                                   A
80                          89                                     B
70                          79                                     C
60                          69                                     D
0                            59                                     F

A relationship from a "scores" table could match to values in this "LetterGrades" table like this:

Scores::PCT < LetterGrades::UpperBoundary AND
Scores::PCT > LetterGrades::LowerBoundary

On a Scores Layout and other layout to tables correctly related to scores, you can simply place the Letter Grade field from LetterGrades on your layout and you'll see the correct Letter Grade for any possible percent score.

This concept can be applied to much more sophisticated situations such as yours by adding additional fields to the table and relationship so that you match to a given record on the basis of subject and level as well in addition to the upper and lower boundary matches.