> What I want is to be able to select the lesson type from a pop-up menu in column 2, and then it derives the values in columns 3 and 4.
That's easy enough, but there's something missing here.
From your description, it sounds like you want Lesson Type to be a pop-up menu - that part is easy.
What's missing, though, is how you calculate 'Matt's Wage'. Is that based on the lesson? That seems to be borne out of the fact that a '30m' lesson is $22, and a '45m' lesson is "$27.50", but a '1h' lesson can be either $60 (Kate) or $33 (everyone else?). It's not clear how you're deriving those figures, especially when 'Mark's cut' is $27, regardless of the student.
If you can clarify that, the rest would be simple.
For simplicity, assuming there's a logical reason behind Kate's values, I would go with a separate table, mostly because this makes it easier to update should the rates change.
Additionally, you say that 'Matt's Wage' should be 60% of the lesson value, and 'Mark's Cut' is 40%, but those figures don't match your table... Assuming a 1h lesson is $60 ($33 + 27%) then 60% of $60 would be $36, not $33. As written, your splits are more like 55/45.
either way, as an example, I created a 'Rates' Table showing the lesson type and a rate:
then, in the main table, I set C3's formula to:
XLOOKUP($B3,Rates::$Lesson Type,$Rate,"",0,1)×0.6
What this does is tell numbers to take the value in B3 (Kate's Lesson Type) and look that up in the Lesson Type column of the Rates table. When it finds a match, it returns the corresponding value from the Rate column in that table. If no match, it returns 0.
It then takes that value and multiplies it by 0.6 (Matt's 60% cut).
The same formula can be used in D3 with a multiplier of 0.4 for Mark's cut.
Fill these two cells (C3 and D3) down the column and you should be set.
Should your rates change, just update the Rates table and the rest will automatically update.
