Add Category in D1 and enter =VLOOKUP(B2,$G$2:$H$4,2,FALSE) in D2, then fill to D7. Create a PivotTable from A1:D7, put Category in Rows and Amount in Values, and choose Sum. The sample totals Furniture 200 and Stationery 150, with Grand Total 350.
On this page9
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
lookup_value | required | The value to look for in the first column of the table. |
table_array | required | The range that holds the data, with the lookup column on the far left. |
col_index_num | required | The column number to return from, counted from the left of table_array (1 = first column). |
range_lookup | optional | FALSE for an exact match, TRUE or omitted for an approximate match on sorted data. |
Related functions
In a spare workbook, name a sheet Practice. Paste the six transactions at A1 and the product catalog at G1 using the copy blocks below. Keep OrderID and Amount numeric; ProductID and Category are text.
Enter Category in D1. Enter =VLOOKUP(B2,$G$2:$H$4,2,FALSE) in D2 and fill D2:D7. The B row reference changes as you fill; the catalog range stays fixed.
Compare all six Category results with the enriched table. Keep repeated products in the transactions, and resolve missing or conflicting catalog keys before accepting the summary.
On Windows with the English ribbon, select only A1:D7, then choose Insert > PivotTable > New Worksheet > OK. Do not include G:H, a blank column or a precomputed Grand Total row in the source.
Place Category in Rows and Amount in Values. Explicitly choose Sum: on Windows use Value Field Settings or Summarize Values By > Sum. Count of Amount is not the required result.
Check Furniture=200, Stationery=150 and Grand Total=350. Category display order can vary; compare labels and totals. After changing source values, refresh the PivotTable.
Enrich six orders, then check category totals
Use a spare worksheet named Practice. Paste the transactions at A1 and the separate catalog block at G1. Add Category in D1, enter the exact-match formula in D2 and fill through D7. Check the six enriched rows before building the category summary from A1:D7.
A1Paste the product catalog at G1
ProductID Category
P01 Stationery
P02 Stationery
P03 FurnitureExact-match Category formula for D2; fill through D7
=VLOOKUP(B2,$G$2:$H$4,2,FALSE)| ProductID | Category |
|---|---|
| P01 | Stationery |
| P02 | Stationery |
| P03 | Furniture |
If columns are cut off, scroll the table sideways.
| OrderID | ProductID | Amount | Category |
|---|---|---|---|
| 1001 | P01 | 40 | Stationery |
| 1002 | P02 | 60 | Stationery |
| 1003 | P01 | 20 | Stationery |
| 1004 | P03 | 150 | Furniture |
| 1005 | P02 | 30 | Stationery |
| 1006 | P03 | 50 | Furniture |
If columns are cut off, scroll the table sideways.
| Category | Sum of Amount |
|---|---|
| Furniture | 200 |
| Stationery | 150 |
| Grand Total | 350 |
If columns are cut off, scroll the table sideways.
- The catalog has 3 unique ProductIDs and the transactions keep all 6 orders. Repeated products in the transactions are valid.
- D2:D7 must read Stationery, Stationery, Stationery, Furniture, Stationery and Furniture. Keep the lookup range $G$2:$H$4 fixed.
- Select only A1:D7 for the PivotTable. Put Category in Rows and Amount in Values, explicitly summarized by Sum.
- Furniture has 2 transactions totaling 200; Stationery has 4 totaling 150. The 6 numeric amounts total 350 and reconcile to Grand Total 350.
- The missing-key and conflicting-catalog variants use separate copies. Restore B7=P03, clear added G5:H5 values and restore the $G$2:$H$4 formula throughout D2:D7 before checking the default totals again.
- After editing source values, refresh. If rows were added below row 7, update the fixed source range first.
Documentation reviewed .
Microsoft: VLOOKUP functionMicrosoft: Create a PivotTable to analyze worksheet dataMicrosoft: Change the summary function for a PivotTable field
Excel for Windows (English ribbon): Select A1:D7, choose Insert > PivotTable > New Worksheet > OK. Put Category in Rows and Amount in Values, then verify Sum.Excel for Mac: Select A1:D7, choose Insert > PivotTable, select New Worksheet and confirm OK. Put Category in Rows and Amount in Values; use Field Settings > Summarize by > Sum and check the totals.
A lookup enriches rows; a PivotTable aggregates them
The catalog maps each ProductID to one Category. VLOOKUP adds that category beside each transaction without combining or deleting the six transaction rows. The PivotTable then groups those enriched rows and sums Amount. Repeated P01, P02 and P03 transactions are intentional: they are separate orders, not duplicate catalog records. This is an ordinary worksheet helper column followed by a PivotTable, not a VLOOKUP formula inside a PivotTable or a Data Model relationship.
Check the lookup and numeric amounts first
The key in B2 is searched in G, the first column of $G$2:$H$4. Column index 2 returns Category from H. FALSE requests an exact match. The dollar signs keep the catalog range fixed while B2 advances through B7. The six Category results must be Stationery, Stationery, Stationery, Furniture, Stationery and Furniture. Keep Amount numeric: if the copied amounts arrive as text, correct their type before summarizing. A PivotTable can otherwise use Count rather than Sum. The English formulas below use comma separators; localized function names or separators can differ.
Missing and conflicting catalog keys
On a separate practice copy, change B7 from P03 to P99 while keeping the catalog unchanged. D7 should return #N/A because P99 has no exact match. Correct the key or supply its valid category; do not hide the error as zero and accept the summary. Restore B7 to P03 before comparing the default totals. For a separate conflicting-catalog check, add P02 at G5 and Furniture at H5, then enter =VLOOKUP(B2,$G$2:$H$5,2,FALSE) in D2 and fill D2:D7. The expanded range now includes conflicting P02 categories in G3:H3 and G5:H5. Resolve one valid category per ProductID before accepting a summary; a lookup does not aggregate all matching catalog rows. No accepted numeric PivotTable total is specified for this conflicting catalog. To reset, clear the added G5:H5 values, restore =VLOOKUP(B2,$G$2:$H$4,2,FALSE) in D2, fill through D7, then rebuild or refresh the default PivotTable.
Refresh the fixed source and follow your platform
The default PivotTable source is Practice!$A$1:$D$7. After changing a value within that range, refresh the PivotTable. Adding rows below row 7 does not expand this fixed reference: update the source range first, then refresh. On Mac, select A1:D7 and choose Insert > PivotTable > New Worksheet > OK. Put Category in Rows and Amount in Values, then use Field Settings > Summarize by > Sum. In Excel for the web, choose Insert > PivotTable and New sheet in the pane; use Summarize Values By > Sum on a value if needed. On Windows, use the dialog and Value Field Settings steps above. Compare the numeric sum with the six source amounts on every platform.
Common mistakes
- Omitting FALSE or letting the catalog range move when filling the formula down.
- Treating repeated transaction ProductIDs as duplicates to remove; all six orders belong in this summary.
- Accepting conflicting catalog categories or hiding a missing-key
#N/Aas zero. - Including the separated catalog or a precomputed Grand Total row in the PivotTable source.
- Accepting Count of Amount, or changing the source and forgetting to update its fixed range and refresh.
Frequently asked questions
Do I put the VLOOKUP formula inside the PivotTable?
No. In this exercise VLOOKUP fills Category in the source worksheet at D2:D7. The PivotTable uses the enriched A1:D7 range and sums Amount by Category.
Why does the PivotTable show Count of Amount?
Check that the source amounts are numbers, then explicitly choose Sum. The required result is Furniture 200, Stationery 150 and Grand Total 350, not a count of the six orders.
Can a ProductID occur more than once?
Yes in the transactions: each order remains a separate row. The catalog must map each ProductID to one valid Category; conflicting duplicate catalog keys must be resolved first.
Why did my source edit not change the PivotTable?
Refresh after changing source values. If you added rows beyond the fixed A1:D7 source, update the source range before refreshing. Check the new lookup results before accepting new totals.