Referencing and Searching another Page Table

%3CLINGO-SUB%20id%3D%22lingo-sub-1552389%22%20slang%3D%22en-US%22%3EReferencing%20and%20Searching%20another%20Page%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552389%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20a%20senior%20employee%20in%20my%20company%20and%20get%20profit%20sharing%20for%20some%20of%20the%20sites%20we%20get%20since%20I%20brought%20in%20the%20client.%20What%20I%20am%20trying%20to%20do%20is%20to%20have%20an%20individual%20page%20for%20each%20site%20to%20exact%20the%20cost%20it%20took%20to%20complete%20and%20to%20find%20our%20profit%20for%20said%20site.%20I%20have%20created%20a%20reference%20page%20with%20two%20tables%2C%20one%20table%20for%20when%20our%20company%20was%20hourly%2C%20and%20the%20other%20with%20salaries%20of%20the%20individual%20techs%20on%20the%20job.%20I%20want%20to%20formulate%20a%20column%20that%20searches%20for%20said%20table%2C%20finds%20the%20name%20of%20the%20employee%2C%20and%20uses%20the%20specific%20rate%20(i.e.%20Travel%2C%20Hourly%2C%20or%20Salary)%20to%20multiply%20against%20the%20number%20of%20hours%2C%20which%20I've%20already%20converted%20to%20decimal%2C%20to%20show%20the%20cost%20of%20having%20that%20tech%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20instance%2C%20John%20gets%20paid%20%2412.50%20to%20travel%20and%20%2414.50%20to%20travel%2C%20and%20a%20table%20labeled%20in%20the%20shows%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CP%3EHourly%20Table%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ETravel%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EWork%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EJohn%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E12.5%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E14.5%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EAnd%20my%20input%20in%20my%20working%20form%20shows%20this%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EExpense%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EExpensee%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EUnit%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3ECost%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E7%2F29%2F2020%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3ETravel%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EJohn%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E.5%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2230px%22%3E7%2F29%2F2020%3C%2FTD%3E%3CTD%20height%3D%2230px%22%3EWork%3C%2FTD%3E%3CTD%20height%3D%2230px%22%3EJohn%3C%2FTD%3E%3CTD%20height%3D%2230px%22%3E7.75%3C%2FTD%3E%3CTD%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2230px%22%3E7%2F29%2F2020%3C%2FTD%3E%3CTD%20height%3D%2230px%22%3ETravel%3C%2FTD%3E%3CTD%20height%3D%2230px%22%3EJohn%3C%2FTD%3E%3CTD%20height%3D%2230px%22%3E.5%3C%2FTD%3E%3CTD%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EI%20want%20to%20input%20this%20function%20to%20search%20on%20my%20first%20page's%20Table%20to%20find%20A)%20the%20Tech%2C%20and%20B)%20the%20rate%20they%20are%20getting%20to%20accurately%20represent%20the%20cost%20of%20sending%20out%20John.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1552389%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1554468%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20and%20Searching%20another%20Page%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F743231%22%20target%3D%22_blank%22%3E%40AnonymousThomas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20in%20Cost%20cell%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(Unit*INDEX(Table1%20Travel%20Work%20range%2C%0AMATCH(%20Expensee%2C%20Table1%20Hourly%20Table%20column%2C%200)%2C%0AMATCH%20(Table%201%20last%20two%20headers%2C%20Expense%2C%200))%2C%0A%22no%20data%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1554610%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20and%20Searching%20another%20Page%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554610%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F743231%22%20target%3D%22_blank%22%3E%40AnonymousThomas%3C%2FA%3E%26nbsp%3B%2C%20there%20are%20a%20few%20options%20to%20do%20the%20lookups.%20Attached%20are%20a%20few%20examples%20including%20the%20Index%20Match%20version%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

TravelWork
John12.514.5

And my input in my working form shows this:

DateExpenseExpenseeUnitCost
7/29/2020TravelJohn.5 
7/29/2020WorkJohn7.75 
7/29/2020TravelJohn.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.

2 Replies

@AnonymousThomas 

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")

@AnonymousThomas , there are a few options to do the lookups. Attached are a few examples including the Index Match version from @Sergei Baklan .