Question

Paws Assisting Today's Youth that uses the existing VLOOKUP function in cell EG as the value function argument, and P20-30 TD1

Answer 1

Note: The data in the excel sheet can be different from the given sheet as some of the data in the given excel sheet is blurred. So, that data can be assumed but this will not affect the final answers.

1).

The VLOOKUP() function is used to look up for a value in another excel sheet and assign the matched value to the selected cell. The required VLOOKUP() formula is shown as follows:

=VLOOKUP(lookup, sheet ! range, column, match)

Lookup: This is the column value which will be looked up in the mentioned sheet named as sheet in the above formula.

Range: This will be the required range in the mentioned sheet to lookup for the required value.

Column: This is the number of the column in which the matched value will be found in the mentioned sheet.

Match: This will tell that the VLOOKUP will set the exact match for the required value.

First the table given in the sheet Department head needs to be sorted and it can be done as follows:

• First select a cell value in the table and press ctrl + A.
• Now, go to the data tab and click on sort and sort by column A in the order A to Z (Smallest to largest).

The required sheet before sorting is look like as follows:

1 Small Appliances Ron Baker 2 Large Appliances David Burk 3 Audio 4 Computers 5 ElectronicsStephanie Givens Paul Keith Timothy Stephens

The required sheet after sorting is look like as follows:

1 Audio 2 Computers 3 Electronics 4 Large Appliances David Burk 5 Small Appliances Ron Baker Paul Keith Timothy Stephens Stephanie Givens

The required formula to create a vlookup formula to look up and return the department head names for each department in the cell G7 can be shown as follows:

G7 = VLOOKUP(E7,DepartmentHead!$A$1:$B$5,2,0)

The look up value will be column E7 and the sheet to look up for the value E7 is department head. The required range in the sheet department head will be A1 to B5 and the matched value will be found in the column number 2 i.e., column B in the sheet department head.

The required sheet after expanding the above formula from G8 to G45 is shown as follows:

Employee # Last Name First Name Gender Department Alvaro Arnold Total Sales Head lookup Small Appliances $6,077.46 Ron Baker Small Appliances $6,504.05 Ron Baker Small Appliances $11,447.40 Ron Baker Small Appliances $3,420.97 Ron Baker Small Appliances $6,830.19 Ron Baker Small Appliances $8,209.89 Ron Baker Large Appliances $20,107.54 David Burk Large Appliances $22,267.74 David Burk Large Appliances $21,800.88 David Burk Large Appliances $22,783 David Burlk Large Appliances $24,204.10 David Burlk Large Appliances $24,283.54 David Burk Large Appliances $24,502.07 David Burk Large Appliances $22,225.89 David Burk 00407 Lorent 08407 Drooks 01340 Synder 04070 Underwood David 64483 Munsell 00654 Brolick 00210 Redden 08480 Headly 65780 Riley 34408 Murris 00551 Unknown 07854 Gussick 35448 Hancock 02546 Foster 06548 Fmux 32488 Mini 00987 Werley 06789 Lamore 25799 Porter 00505 Faust 06548 Burk 05487 Foul 00325 Mills 32057 Salcito Eugene M Jamica Jemnn 1,745.55 Paul Keith $2,295.79 Timothy Stephens $1,123.42 Paul Keith uters Juan $951.19 Paul Keith $1,025.14 Paul Keith $1,289.27 Paul Keith $1,893.67 Paul Keith $9,484.44 Timothy Stephens $7,871.69 Timothy Stephens Kathi Louise Lyann Marco Mario uters uters uters uters $12,854.00 Timothy Stephens $11,060.55 Timothy Stephens Small Appliances $8,181.55 Ron Baker 32488 Malluthium Mixel 02547 Goldchaium Mitch 08455 Garonlez Patrick 83508 Scott 87851 Bdwards Richard 06875 Cherly 01279 Harris 45670 McMalos Steven 00657 Stanmon Susan 00104 Skelton 03483 Pruitt 03589 Owens 36445 Pabel uters uters uters $7,055.60 Timothy Stephens 1,915.41 Timothy Stephens $14,317.25 Timothy Stephens $1,921.24 Paul Keith Electronics Electronics Electronics M $11,405.55 Stephanie Givens $8,041.70 Stephanie Givens $4,728.39 Stephanie Givens $1,002.00 Paul Keith $1,748.86 Paul Keith Roherta F Ronald Small Appliances $6,136.23 Ron Baker Electronics Electronics Electronics $9,510.22 $3,172.59 $9,498.65 Vince Stephanie Givens Stephanie Givens Stepha nie Givens Gender Gender

2).

The required nested IF formula to give the same results as of the results obtained from previous formula in the cell H7 can be shown as follows:

H7 = IF(E7="Audio","Paul Keith",IF(E7="Computers","Timothy Stephens",IF(E7="Electronics","Stephanie Givens",IF(E7="Large Appliances","David Burk",IF(E7="SmallAppliances","Ron Baker","")))))

The above formula is using nested if conditions to check if the value in the column E7 is any value contained in the column A in the sheet department head, then the corresponding value of the column B will be assigned to the cell H7.

The required sheet after expanding the above formula from H8 to H45 is shown as follows:

Employee# Last Name First Name Gender Department Total Sales Head lookup Head Nested IF Small Appliances $6,077.46 Ron Baker 00407 Lorent 08407 Drooks 01340 Synder Brett 04070 Underwood David 64483 Munsell 25447 Hewitt 00654 Brolick 00210 Redden Eugene MLarge Appliances $22,267.74 David Burk 08480 Headl 65780 Riley 34408 Murris 00551 Unknown JamicaM Large Appliances $24,283.54 David Burk 07854 Gussick 35448 Hancock JemnnF 02546 Foster 06548 Fmux 32488 Mini 00987 Werley 06789 La more 25799 Porter 00505 Faust 06548 Burk 05487 Foul 00325 Mills 32057 Salcito 08587 Ceak 32488 Malluthium Mixel 02547 Goldchaium Mitch 08455 Garonle Patrick 83508 Scott 87851 Bdwards Richard M 06875 Cherly 01279 Harris 45670 McMalos Steven 00657 Stanmon Susan 00104 Skelton TrescaF 03483 Pruitt 03589 Owens 36445 Pabel Small Appliances $11,447.40 Ron Baker Small Appliances $3,420.97 Ron Baker Small Appliances $6,830.19 Ron Baker Ernest Large Appliances $20,107.54 David Burk David Burk David Burk David Burk David Burk David Burk David Burk David Burk David Burk Paul Keith Frank M Large Appliances $21,800.88 David Burk M Large Appliances $24,502.07 David Burk Large Appliances $22,225.89 David Burk 1,745.55 Paul Keith $2,295.79 Timothy Stephens Timothy Stephens $1,123.42 Paul Keith Computers $951.19 Paul Keith 1,025.14 Paul Keith $1,289.27 Paul Keith $1,893.67 Paul Keith 9,484.44 Timothy Stephens Timothy Stephens $7,871.69 Timothy Stephens Timothy Stephens Paul Keith Paul Keith Paul Keith Paul Keith Paul Keith Kathi Computers Computers Computers Computers Small Appliances $8,181.55 Ron Baker Computers Computers Computers $12,854.00 Timothy Stephens Timothy Stephens $11,060.55 Timothy Stephens Timothy Stephens $7,055.60 Timothy Stephens Timothy Stephens 1,915.41 Timothy Stephens Timothy Stephens $14,317.25 Timothy Stephens Timothy Stephens 1,921.24 Paul Keith Paul Keith $11,405.55 Stephanie Givens Stephanie Givens Electronics Electronics Electronics $8,041.70 Stephanie GivensStephanie Givens $4,728.39 Stephanie GivensStephanie Givens 1,002.00 Paul Keith $1,748.86 Paul Keith Roherta Paul Keith Paul Keith Electronics Electronics Electronics 9,510.22 Stephanie GivensStephanie Givens 3,172.59 Stephanie GivensStephanie Givens 9,498.65 Stephanie Givens Stephanie Givens