9) The table in the VLOOKUP worksheet shows that average salaries of the employees in the same in...

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 cells

Home 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 Texas

Home 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 Texas

HomeInsert 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