How to Fix VLOOKUP Errors When SharePoint Query Values Are Stored as Text
Question details
The user needs to fix VLOOKUP function failures caused by a data-type mismatch between numeric lookup values and text-formatted numeric codes returned from a SharePoint query.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Using VLOOKUP to match data from a SharePoint query where numbers are imported as text.
- Observed behavior
- VLOOKUP returns an #N/A error because it cannot match a number formatted as text with an actual number.
Ensure that your SharePoint data query has completely refreshed and identify which column contains the text-formatted numbers before modifying the formulas.
Use the VALUE() Function to Align Data Types in the Formula
Modify your VLOOKUP formula to dynamically convert the text-formatted SharePoint values into numbers, or vice versa, without altering the source data.
When SharePoint returns numbers as text, VLOOKUP fails to match them with actual numbers in your spreadsheet. You can use helper functions within your formula to bridge this gap.
Select a blank cell and type `=ISTEXT(A2)` for your lookup value and `=ISTEXT(B2)` for the SharePoint cell. If one returns TRUE and the other FALSE, you have a mismatch.
If your lookup value is text but the SharePoint array contains numbers, wrap your lookup reference in the VALUE function: `=VLOOKUP(VALUE(A2), table_array, col_index, FALSE)`.
If your lookup value is a number but the SharePoint array contains text, append an empty string to convert your number to text: `=VLOOKUP(A2&"", table_array, col_index, FALSE)`.
Convert the SharePoint Query Column to Numbers
Use the Text to Columns feature to permanently convert the imported text values into standard numbers.
Solve VLOOKUP Errors Instantly in WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools and seamless compatibility with Microsoft Excel formulas. Easily handle VLOOKUP, check data types, and manage imported queries without hassle.
- 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your SharePoint query.
- 2. Convert via Smart Warning: Select the cells with text-numbers, click the floating yellow warning icon, and choose 'Convert to Number'.
- 3. Apply VLOOKUP: Type your standard `=VLOOKUP()` formula. WPS will smoothly process the matched data types without returning #N/A errors.
Frequently Asked Questions
Why does VLOOKUP return #N/A when the cell values look identical?
VLOOKUP is strictly sensitive to data types. Even if '123' (stored as text) and 123 (stored as a number) look identical on screen, the spreadsheet engine treats them as entirely different entities. Ensuring both match in type is required for a successful lookup.
Can I stop SharePoint queries from importing numbers as text?
SharePoint often exports identifiers (like IDs or zip codes) as text to preserve leading zeros. To prevent this, you can adjust the column data types in your Power Query or data connection settings before the data loads into the worksheet.
Is there a fast way to check if a cell is text or a number?
Yes. You can use the built-in functions `=ISTEXT(cell_reference)` or `=ISNUMBER(cell_reference)`. Alternatively, without formulas, numbers align to the right by default in most spreadsheet software, while text aligns to the left.
