9) The table in the VLOOKUP worksheet shows that average salaries of the employees in the same in...
lel 困掣 匯 0曰
Average Salaries in three satates based on the Employee Title OklahomaTexas Colorado $65,000 $75,000 120,000 $70,000 $90,000 $150,000 $75,000 $85,000 $175,000 Analyst Associate 10 12 13 14 15 16 17 18 19 20 23 25 26 27 Data Vlook up
Average salaries in three states based on employee title oklahoma Texas colorado Analyst $ 65,000.00 $ 70,000.00 $ 75,000.00 Associate $ 75,000.00 $ 90,000.00 $ 85,000.00 Vp $ 120,000.00 $ 150,000.00 $ 175,000.00
First do convert above information as below given table by creating salary level column and putting corresponding designation and region in the C and D. Then create Key Column by using following formula in cell B5 =CONCATENATE(C5,D5) and drag for whole column.
Since first table is not properly copied here i have created one sample of my own as below
As above i created Key Column here using =CONCATENATE(B2,C2) in cell A2. Since here data is not present for Salary level Column and can be copied here using vlookup function.we will use vlookup function in cell D2
Here in cell D2 i used =VLOOKUP(A2,Sheet11!B4:E13,4,0)
A2 contains the Key column then comma and then
I put reference data SHEET11!B4:E13 where from I want to get the data for salary level. Don't forget to press F4 to fix the reference data so it look like as SHEET11!$B$4:$E$13 and then put comma
4 signify the no of column after key column you want data from reference data as below and then put comma
0 is for Exact Match, Exact data will be copied
now you cant drag this formula for all the cellsHome Insert Page Layout Formulas Data Review View Cut Copy Format Painter Calibri wrap Text General For board Font Alignment Number B5 Region oklahoma Designation Anal Associate Ke oklahomaAnal oklahomaAssociateoklahoma oklahomaV TexasAnal TexasAssociate TexasV coloradoAnalvyst coloradoAssociate coloradoV Salary level oklahoma Texas Texas Texas colorado colorado colorado 65,000.00 75,000.00 120,000.00 70,000.00 90,000.00 150,000.00 75,000.00 85,000.00 175,000.00 Anal Associate Anal Associate凸 HomeInsert Page Layout Formulas t Rie View ぬCut | _ _ _1ター Wrap Text Calibri ー Copy - Merge & Center B 1 Uy田ㄧ .스- Paste Format Painter Font Alignment Clipboard B7 oklahoma Salary level Designation Analyst Associate Vp Analyst Associate Vp Analyst Associate Vp Region Texas 1 Key 2 TexasAnalyst 3 oklahomaAssociate oklahoma 4 ColoradoVp 5 oklahomaAnalyst oklahoma 6 TexasAssociate 7 oklahomaVp 8 ColoradoAnalyst Clorado 9 ColoradoAssociate Colorado 10 TexasVp Colorado Texas oklahoma TexasHome Insert Page Layout FormulasData Review View ぬCut Ea Copy プFormat Painter Blu,田 E E Calibri Paste Clipboard Font Alignment D2 VLOOKUP(A2,Sheet11!B4:E13,4,0) Region Texas 1 Key 2 TexaSAnalyst 3 oklahomaAssociate oklahoma 4 ColoradoVp Designation Analyst Associate Vp Analyst Associate Vp Analyst Associate Vp Salary level $70,000.00 Colorado Texas oklahoma 6 TexasAssociate 7 oklahomaVp 9 ColoradoAssociate Colorado 10 TexasVp TexasHomeInsert Page Layout Formulas Data Review View Cut Copy Format Painter board B5 Calibri Wrap Text General Font Number fC5&""&D5 Region oklahoma Designation Analyst Associate Vp Analvst Associate Vp Analyst Associate Vp Ke Salary level oklahomaAssociate oklahoma oklahomaVp TexasAnalyst TexasAssociate TexasVp coloradoAnalyst coloradoAssociate colorado coloradoVp oklahoma Texas Texas Texas colorado 65,000.00 75,000.00 120,000.00 70,000.00 90,000.00 150,000.00 75,000.00 85,000.00 colorado