How to combine Excel VLOOKUP() and PivotTable for simple solutions

The combination of features often increases the flexibility and efficiency of the solution.

istock-524149458excel.jpg

Photo: fizkes, Getty Images / iStockphoto

Many solutions require more than a simple function or filter. For example, if your company applies stipends for travel, you will likely add the same amount per employee for travel days. Now, suppose your company pays a different salary for each job site and the employee can earn more than one salary in a day? The solution is not as difficult as it sounds, but it is more complicated than using an IF() statement to add a fixed amount in days of travel.

In this article, we’ll combine the VLOOKUP() function, a data validation list, and a PivotTable to create a simple app that tracks salary bonuses for employees when working on offsite job sites. The structure is flexible enough to accommodate employees working in multiple off-site locations in a single day. The VLOOKUP() function will return the correct salary for each signed record. The data validation control will restrict input to specific locations, avoiding misspellings and invalid locations. Finally, the PivotTable will return the total salaries earned for each employee by employee and date.

I’m using Excel (desktop) for Office 365, but you can use earlier versions. You can work with your own data or download the .xlsx and . xls. Unlike many solutions, you can build this one and use it in the browser version. You don’t need to know anything about the VLOOKUP() function or how to create a validation list or PivotTable but learning about these features will be helpful.

See: Choosing a Windows 7 Exit Strategy: Four Options (Tech Pro Research)

the problems

The simple paper shown in Figure A It contains two table objects. The person on the left keeps track of each employee’s working hours at specific job sites. The table on the right lists each position and their daily salary. You can save the amounts and include them in an hourly log, but this invites trouble: you may enter the wrong amount, and any time you enter values ​​manually you risk misspellings. By having a fixed list, you ensure that your data is correct. But how do you match them? The simplest answer is to use the VLOOKUP() function.

excelvlookuppta.jpg

Figure A: Use the VLOOKUP() function to return the salary for each hourly record.

There are a few things to note before we proceed:

  • Each employee can visit one or more job sites in one day. This means that we will have to add these amounts to one total daily salary.
  • Each employee may spend a few hours or the entire day in the home office, which has no salary.
  • Data entry is very important. The value of the position in the hourly list must match the value of the position in the payroll to avoid errors.

Throughout the article, I will refer to the list on the left as the hourly list and the list on the right as the payroll. We work with table objects so that we can easily modify the Stipend list without updating its references. To convert a normal data range to a table object, do the following:

  1. Click anywhere within the data range.
  2. Click the Insert tab, and then click Table in the Tables group.
  3. Indicate if the data has headers (demo data does).
  4. Click OK.

If you’re working with your own data, you don’t have to use table objects, but the rest of this article assumes you are.

VLOOKUP() function

The quickest way to add a salary amount for each job position to the hours list is to add the VLOOKUP() function using the following formula:

VLOOKUP(lookup_value, table, column_index, range)

where look for value is the cell or range that contains the value in the watch list you’re looking for – location (column E) in this case; table defines lookup table – H4:I6 (payroll table minus heads); column_index Represents the column containing the values ​​you want to return in relation to the lookup value – Stipend and elegant – tidy TRUE / FALSE that forces an exact (or not) match.

Now, let’s enter the following VLOOKUP() function in cell F4:

=VLOOKUP([Location],Table2,2,FALSE)

If you’re not working with table objects, enter this function instead:

=VLOOKUP($E$4:$E$10,$H$4:$I$6,2,FALSE)

Note that the two ranges (for a normal data range) must be absolute references.

Figure B Shows the results after formatting the new column as currency and adding the header text. The table will be automatically modified to include the new column.

excelvlookupptb.jpg

Figure B: Add a VLOOKUP() column to an hourly table.

We have two apparent problems: Home Office and McValey (row 8) both display the same error message. The error in row 4 is easy to fix; The error in grade 8 will require more work.

Problem 1

Excel returns an error in row 4 because there is no matching value for the position in the payroll. To fix this error, simply add a new record to the payroll, as shown in Figure C. As you can see, this simple fix takes care of the error in row 4. That’s why I chose to use table objects – the VLOOKUP() function updates automatically to include the new row – you don’t have to modify the function.

excelvlookupptc.jpg

Figure C: Each hourly position in the list needs a row in the payroll.

Problem 2

It was easy to troubleshoot and fix the error in row 4. Can you determine why VLOOKUP() returned row 8 an error? There is a record of McValley’s job location on the payroll, so the next place to look is the location value in courier List. do you match, exactly, value in payroll? Oh! That’s right, it’s missing the letter l – the site is misspelled.

The easiest solution is to fix the typo, but that won’t remove new typos in the future. Instead, let’s add a data validation list to an hourly table. Doing so will limit users to the items in the list and avoid future errors. Specifically, the list will include the position values ​​from the payroll, and you will enter the site using the list rather than manually typing each position.

Add the list as follows:

  1. Select E4: E10 (If you add the checklist to E3, you will not add a control to existing or new records.)
  2. Click the Data tab, and then click Data Validation in the Data Tools group.
  3. In the resulting dialog, choose a list from the Allow drop-down list.
  4. Specify the location values ​​in the salary group in the source control (figure d).
  5. Click OK.

excelvlookupptd.jpg

Figure D: This type of checklist restricts input to only the values ​​in the source list.

Now, select E8 and using the Validation Control dropdown, enter McValley, as shown in Figure E. As you can see, once you correct the spelling of the location value, the VLOOKUP() function works as expected. In addition, the control is dynamic – more table charm. Payroll update will also update the validation control list. You can’t do this with a normal data range.

excelvlookuppte.jpg

Figure E: Use the checklist to fix the error.

To learn more about VLOOKUP() errors, read Troubleshooting VLOOKUP() syntax.

daily totals

After fixing the two issues inherent in the original structure, we now have the total payroll for each signed record. However, the current hourly list structure does not return a file daily total per employee. Remember that each employee can work at more than one location on the same day. For example, E-3 worked at two locations on May 1 and both locations have applicable pay rates. E-3 should receive a total of $75 in salary rates for May 1 – not $40 or $35 (individual site rates).

There are a number of ways to accomplish this, but perhaps the easiest is to use a PivotTable. To do this, click anywhere inside the list every hour and click the Insert tab. Then do the following:

  1. Click the PivotTable in the Tables group. In the resulting dialog box, click OK.
  2. Click inside the PivotTable window, which will display the List pane.
  3. In the Fields pane, check the Employee and Location Graphic fields (shape and).

excelvlookupptf.jpg

Figure F: Show the total amount of salary for each employee.

As it stands, the PivotTable displays grand totals for each employee. as you see in shape and Total E-3 is $75 – 2 locations added on the same day. If you need a daily subtotal, add the date field to the PivotTable, as shown in shape g.

excelvlookupptg.jpg

Figure G: Add the date field to display a subtotal for each day.

To learn more about PivotTables, read Get the most out of Excel PivotTables with these helpful tips.

Combo solution

As is often the case, an effective solution requires a combination of efforts. In this case, we used the VLOOKUP() function to add important details to the tracking list. In particular, we have added a dedicated amount for each job site. Next, we used a PivotTable to add these salary rates in two different ways. In a future article, we will continue this solution scenario using Power Query.

Send me your question about Office

I answer readers’ questions when I can, but there is no guarantee. Do not send files unless asked to do so; Initial requests for help that arrive with attached files will be deleted as unread. You can submit screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, you likely won’t get the response “Please troubleshoot the workbook and fix the error”, but “Can you tell me why this formula isn’t returning the expected results?” Probably. Please mention the app and version you are using. I do not compensate TechRepublic for my time or expertise when helping readers, nor do I charge a fee from the readers I help. You can contact me at susansalesharkins@gmail.com.

see also