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

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

Many solutions require more than a simple function or filter. For instance, if your company applies stipends for travel, you probably add the same amount for every employee for travel days. Now, let’s suppose that your company pays a different stipend for each job site location and that an employee could earn more than one stipend in a single day? The solution isn’t as difficult as it sounds, but it’s more complicated than using an IF() statement to add a fixed amount on travel days.

In this article, we’ll combine a VLOOKUP() function, a data validation list, and a PivotTable to create a simple application that tracks stipend awards for employees when working at off-site job locations. The structure is flexible enough to accommodate employees who work at multiple off-site locations in a single day. The VLOOKUP() function will return the correct stipend for each location record. A data validation control will restrict input to specific sites, avoiding typos and invalid sites. Finally, a PivotTable will return stipend totals earned for each employee by employee and date.

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

SEE: Choosing your Windows 7 exit strategy: Four options (Tech Pro Research)

The problems

The simple sheet shown in Figure A contains two Table objects. The one on the left tracks the hours each employee works at specific work sites. The Table to the right lists each site and its daily stipend. You could memorize the amounts and list them with the hourly record, but that invites trouble: You might enter the wrong amount, and anytime you enter values manually you risk typos. By having a stable list, you ensure the validity of your data. But, how do match them? The simplest answer is to use a VLOOKUP() function.

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

There are a few things worth noting before we continue:

  • Each employee might visit one or more job sites in a single day. That means we’ll have to add those amounts into a single daily stipend total.
  • Each employee might spend a few hours or the entire day at the home office, which has no stipend.
  • Data entry is very important. The location value in the hourly list must match a location value in the stipend list to avoid errors.

Throughout the article, I’ll refer to the list on the left as the hourly list and list on the right as the stipend list. We’re working with Table objects so we can easily modify the Stipend List without updating its references. To convert a regular data range into a Table object, do the following:

  1. Click anywhere inside the data range.
  2. Click the Insert tab and then click Table in the Tables group.
  3. Indicate whether the data has headers (the demonstration data does).
  4. Click OK.

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

(Video) How to use VLOOKUP to merge tables

The VLOOKUP() function

The quickest way to add a stipend amount for each job site to the hourly list is to add a VLOOKUP() function using the following syntax:

VLOOKUP(lookup_value, table, column_index, range)

where lookup_value is the cell or range that contains the value in the hourly list that you’re looking up–Location (column E) in this case; table identifies the lookup table–H4:I6 (the stipend list Table minus the headers); column­_index represents the column that contains the values you want to return in relation to the lookup value–Stipend, and range is a TRUE/FALSE value that forces (or not) an exact match.

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


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


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

Figure B shows the results after formatting the new column as Currency and adding header text. The Table will automatically adjust to include the new column.

(Video) how to do vlookup function in excel ( multiple sheets)/ v lookup with different sheet in excel.

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

We have two apparent problems: The home office and McValey (row 8) both return the same error message. The error in row 4 is easy to fix; the error in row 8 will require a bit more work.

Problem 1

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

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

Problem 2

The error in row 4 was easy to troubleshoot and fix. Can you determine why the VLOOKUP() function for row 8 returns an error? There’s a record for the McValley job site in the stipend list, so the next place to look is the location value in the hourly list. Does it match, exactly, the value in the stipend list? Oh! That’s right, it’s missing an l–the location is misspelled.

The easiest solution is to fix the typo, but that won’t eliminate new typos in the future. Instead, let’s add a data validate list to the hourly table. Doing so will limit users to items in the list and avoid future errors. Specifically, the list will include the location values from the stipend list, and you’ll enter the location using the list instead of manually typing each location.

Add the list as follows:

  1. Select E4:E10 (If you add the validation list to E3, it won’t 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 List from the Allow dropdown.
  4. Indicate the location values in the stipend group in the Source control (Figure D).
  5. Click OK.

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

Now, select E8 and using the validation control’s drop-down list, 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 magic. Updating the stipend list will also update the validation control’s list. You can’t do that with an ordinary data range.


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

To learn more about VLOOKUP() errors, read Troubleshoot VLOOKUP() formula gotchas.

The daily totals

After fixing the two problems inherent to the original structure, we now have stipend totals for each site record. However, the current hourly list structure doesn’t return a daily total for each employee. Remember, each employee can work at more than one location in the same day. For example, E-3 worked at two sites on May 1 and both sites have applicable stipend rates. E-3 should receive a total of $75 in stipend rates for May 1–not $40 or $35 (the individual location rates).

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

  1. Click PivotTable in the Tables group. In the resulting dialog, click OK.
  2. Click inside the PivotTable frame, which will display the list pane.
  3. In the fields pane, check the Employee and Site Stipend fields (Figure F).

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

As is, the PivotTable displays grand totals for each employee. As you can see in Figure F, E-3’s total is $75–the addition of two sites on the same day. If you need a daily subtotal, add the Date field to the PivotTable, as shown in Figure G.

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

To learn more about PivotTable objects, read Get the most out of your Excel PivotTables with these handy tips.

Combo solution

As is often the case, an efficient solution required a combination of efforts. In this case, we used the VLOOKUP() function to add an important detail to a tracking list. Specially, we added a stipend amount for each location worked. Then, we used a PivotTable to add those stipend rates in two different ways. In a future article, we’ll continue this solution scenario by using Power Query.

(Video) How to Join Tables using VLOOKUP formula in Excel

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at

See also

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

Daily Tech Insider Newsletter

Stay up to date on the latest in technology with Daily Tech Insider. We bring you news on industry-leading companies, products, and people, as well as highlighted articles, downloads, and top resources. You’ll receive primers on hot tech topics that will help you stay ahead of the game.

Delivered Weekdays

Sign up today


1. Use a PivotTable and Vlookup to find out weekly revenue by Chris Menard
(Chris Menard)
2. 7 Ways to Use Vlookup in Excel
(My E-Lesson)
3. Excel VLOOKUP In Pivot Table Calculated Field? - 2456
4. MS Excel - Vlookup in Excel Video Tutorials
(Tutorials Point)
5. How to apply VLOOKUP on Large / Big Data in Excel (Hindi)
6. Advanced Pivot Table Techniques: Combine Data from Multiple Sheets in Excel
(Leila Gharani)
Top Articles
Latest Posts
Article information

Author: Annamae Dooley

Last Updated: 01/30/2023

Views: 5555

Rating: 4.4 / 5 (45 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Annamae Dooley

Birthday: 2001-07-26

Address: 9687 Tambra Meadow, Bradleyhaven, TN 53219

Phone: +9316045904039

Job: Future Coordinator

Hobby: Archery, Couponing, Poi, Kite flying, Knitting, Rappelling, Baseball

Introduction: My name is Annamae Dooley, I am a witty, quaint, lovely, clever, rich, sparkling, powerful person who loves writing and wants to share my knowledge and understanding with you.