'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Unexpected Numbers in Excel VLOOKUP and PivotTables
Question details
The user needs to find out why an unexpected number is appearing in both VLOOKUP formulas and PivotTable results.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Analyzing or summarizing data using VLOOKUP formulas and PivotTables.
- Observed behavior
- Both the VLOOKUP formula and the PivotTable return an unexpected number that does not seem to belong, suggesting a hidden anomaly in the source data.
Before troubleshooting your formulas or PivotTable settings, ensure that your workbook does not have active filters applied that might temporarily obscure parts of your source data.
Unhide Rows and Verify Your Source Data Range
Since both VLOOKUP and the PivotTable rely on the same source data (like a specific Suppliers range), the unexpected number is almost certainly hidden inside the source data itself rather than being artificially generated.
Often, rows in a source data range are hidden manually or by previous filtering. PivotTables and VLOOKUP will still read these hidden rows, causing unexpected values to appear in your final reports.
Go to the worksheet containing the source data range (e.g., the 'Suppliers' sheet) referenced by your VLOOKUP or PivotTable.
Click the small triangle in the top-left corner of the worksheet (between column header A and row header 1), or simply press Ctrl + A on your keyboard.
Right-click on any row number on the left side of the screen and select 'Unhide' from the context menu to reveal any hidden data.
Press Ctrl + F to open the Find dialog, type the unexpected number, and locate it in the source data. Delete or correct the incorrect entry.
Return to your PivotTable, right-click anywhere inside it, and select 'Refresh' to update the table with the corrected source data.
Easily Manage Source Data and PivotTables with WPS Spreadsheet
WPS Spreadsheet provides intuitive tools for finding hidden data, managing named ranges, and refreshing PivotTables. It allows you to seamlessly track down formula issues and is completely free to use.
- 1. Open Your File: Launch WPS Spreadsheet and open your existing Excel workbook.
- 2. Unhide Data: Select the source data area, right-click the row numbers, and click 'Unhide' to reveal all records.
- 3. Update the PivotTable: Navigate to your PivotTable sheet, right-click inside the table, and select 'Refresh' to apply the clean data.
Frequently Asked Questions
Why does my PivotTable still show deleted data?
PivotTables store a background cache of your data to improve performance. If you delete a value from your source data, you must right-click the PivotTable and select 'Refresh' to update the cache and remove the old data from your view.
How do I fix a VLOOKUP returning a completely wrong value?
Ensure the 'range_lookup' argument in your VLOOKUP formula is set to FALSE or 0 for an exact match (e.g., =VLOOKUP(A1, B:C, 2, FALSE)). If it is omitted or set to TRUE, Excel will return an approximate match, which frequently results in an unexpected number.
How can I easily tell if there are hidden rows in my spreadsheet?
Look at the row numbers on the left side of your spreadsheet. If you see a gap in the numbering sequence (for example, it skips from row 10 to row 15), the rows in between are hidden.
Does changing the source data automatically update a VLOOKUP?
Yes, a VLOOKUP formula calculates dynamically. The moment you change the data in the source cells it references, the VLOOKUP result will update instantly without needing a manual refresh, unlike a PivotTable.
