Fix SUM Not Adding VLOOKUP Results in Excel
Question details
The user needs SUM to include numeric values returned by VLOOKUP.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses VLOOKUP to retrieve values that are then added with SUM.
- Observed behavior
- The returned values appear to be numbers but are stored as text, so SUM does not include them.
Select a VLOOKUP result cell and check the formula bar to identify the lookup formula. You can test whether its result is a number with =ISNUMBER(C2), replacing C2 with the result cell; FALSE indicates it is not numeric.
Multiply the VLOOKUP Result by 1
Use this method to convert numeric text returned by VLOOKUP into numbers that SUM can add.
Select the cell containing VLOOKUP and click the formula bar. Add *1 after the closing parenthesis, for example: =VLOOKUP($B17,Table_2[#All],COLUMN('Table 2'!B3),0)*1.
Press Enter, then drag the cell's fill handle down to update the other VLOOKUP formulas in the range.
Select the total cell and confirm its SUM formula includes the updated result cells, such as =SUM(C2:C10).

Wrap the VLOOKUP Result in VALUE
Use VALUE when you prefer an explicit function to convert a numeric text result.
Convert VLOOKUP Results for SUM in WPS Spreadsheets
In WPS Spreadsheets, multiply a VLOOKUP result by 1 to convert numeric text, then use SUM to total the converted cells.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your workbook.
- 2. Convert the lookup result: Select a VLOOKUP result cell and add *1 to the end of its formula in the formula bar, such as =VLOOKUP(B2,$E$2:$F$100,2,FALSE)*1.
- 3. Fill the formula down: Press Enter, then drag the fill handle down through the other result cells.
- 4. Add the results: Select the total cell and enter a SUM formula for the result range, such as =SUM(C2:C10).

Frequently Asked Questions
Why does SUM ignore my VLOOKUP results?
VLOOKUP may return values stored as text in the source table. SUM ignores text in referenced cells, even when it looks like a number.
How can I tell whether a lookup result is numeric?
Enter =ISNUMBER(C2) in an empty cell, replacing C2 with the VLOOKUP result cell. TRUE means the result is a number; FALSE means it is not.
Why do I get #VALUE! after multiplying by 1?
The returned text may contain characters or formatting that cannot be converted to a number. Check the corresponding source cell and correct the value before recalculating.
Will multiplying by 1 fix a #N/A lookup error?
No. #N/A usually means VLOOKUP did not find a match. Check the lookup value, table range, and match mode in the VLOOKUP formula.




