Sounds like you have a single record where you really should have several so that you only have one name field and associated amount to a given record. Then you can set up a relationship that matches by two pairs of fields, on for ID and one for name.
Otherwise, you are looking at a fairly complex case function where the clauses of the Case function compare the name to the related Name1, Name2... fields with the result clauses then returning the value of the associated amount field.
Case ( NameField = RelatedTable::Name1 ; RelatedTable::amount1 ;
NameField = RelatedTable::Name2 ; RelatedTable::amount2 )
I'm not sure where I could make one record several and still serve my purposes, but I think I may have confused the issue by simplifying too much. Let me give you some better context. I have two tables, an Ingredients table (the table where the total field resides) and a Batches table which contains Name 1 and Name 1 amount (actually Ingredient 1 and Quantity Used 1) and Ingredient 2 and Quantity Used 2 and so on. The idea is to, with the total field, keep track of the amount of the ingredient the people using the database have on hand, based off of the original quantity and how much of each ingredient is used.
The calculation I've been trying to use goes something like this:
Original Total Quantity - If (Ingredient 1 = Ingredient Name; Quantity Used 1; 0) - If (Ingredient 2 = Ingredient Name; Quantity Used 2; 0) and so on...
The issue with this is, as mentioned, that it's taking only Ingredients and Quantities Used from the record with the same serial number as the Ingredient. Since multiple batches could use some of the same ingredients I'm trying to find a way to make sure the total is calculated based off of fields that fit the bill in all records of Batches.