Jul 29 2020 12:31 AM - edited Jul 29 2020 12:32 AM
I am a senior employee in my company and get profit sharing for some of the sites we get since I brought in the client. What I am trying to do is to have an individual page for each site to exact the cost it took to complete and to find our profit for said site. I have created a reference page with two tables, one table for when our company was hourly, and the other with salaries of the individual techs on the job. I want to formulate a column that searches for said table, finds the name of the employee, and uses the specific rate (i.e. Travel, Hourly, or Salary) to multiply against the number of hours, which I've already converted to decimal, to show the cost of having that tech there.
So for instance, John gets paid $12.50 to travel and $14.50 to travel, and a table labeled in the shows:
Hourly Table | Travel | Work |
John | 12.5 | 14.5 |
And my input in my working form shows this:
Date | Expense | Expensee | Unit | Cost |
7/29/2020 | Travel | John | .5 | |
7/29/2020 | Work | John | 7.75 | |
7/29/2020 | Travel | John | .5 |
I want to input this function to search on my first page's Table to find A) the Tech, and B) the rate they are getting to accurately represent the cost of sending out John.
Jul 29 2020 03:44 PM
You may use in Cost cell something like
=IFERROR(Unit*INDEX(Table1 Travel Work range,
MATCH( Expensee, Table1 Hourly Table column, 0),
MATCH (Table 1 last two headers, Expense, 0)),
"no data")
Jul 29 2020 05:59 PM
@AnonymousThomas , there are a few options to do the lookups. Attached are a few examples including the Index Match version from @Sergei Baklan .