How to create a "vlookup" like in Excel
More Lookups Q
I run a number of English projects that last for 4 weeks each. Each project cosists of a number of Classes (project A has 2 classes 10:00-11:00 is English level 1, 11:00-12:00 is English level 2). For each class I have to give a % commission to my partner based on the number of student numbers enrolled. Also For each Project, the % commission might be different. The commissions might change over time too, so I want a record of what was used before.
When I used Excel I had 1 spreadsheet for each project, and a "table" to tell me what the commission was. I used a "vlookup" formula to find and return the % commission.
So I had something that looked like this :
Student Number Commission
The problem: If the commissions were always all the same, then I know how to do it. But becuase it can change, I need to make a record of the % for each project.
I created a table for commissions:
1StuPercnt (to record % for 1 student)
2StuPercnt (to record % for 2 students)
3StuPercnt (to record % for 3 students)
4StuPercnt (to record % for 4 students)
and a project table to manage the projects:
MyProfit (This would be StudentFees x Commission)
What I would like:
When viewing the Project_Table I would like to enter the student number then have the commission field update automatically (from the commission table) and then show me MyProfit.
#1 Script: Iknow i can write a script and have it search the "commissions" table and then find the corrrect field, but that just seems complicated... also for scripts do I have to click a button to run it each time?
#2 Coded into the formula itself. BUT I dont want this as its compelex to update, change each time i open up a new project.
#3 Different Table design
Commission Table could be
but I find this "inefficient" (i.e. why can't I just store them all in one record? vs Creating 4 records for each project?) I also found this a bit troublesome to fill in each time i start a program. When I create a new project I can sometimes copy values from a previous project. But it seems easier if i had all the commission in one record rather than a separte record for each %.
I did search forum for the answers, but they seem to be a bit differnt from my situation:
Im still new to FM so apologies if its a real noob Q.. but i really cant seem to figure it out.