'%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 Excel #N/A Errors with LOOKUP and Exact Matches
Question details
The user is experiencing #N/A errors when using the LOOKUP function to retrieve data, even when the lookup value appears to exist in the source table.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Pulling data from source tables based on a selected name using a LOOKUP formula.
- Observed behavior
- The formula =LOOKUP($FC$4,$R$4:$R$17,$S$4:$S$17) returns an #N/A error for certain names, despite testing different letter cases.
Ensure your dataset does not contain invisible formatting characters or leading and trailing spaces, which frequently cause exact-match lookup formulas to fail.
Use VLOOKUP for Exact Matches
Switch from the approximate LOOKUP function to VLOOKUP with the FALSE argument to force an exact match.
The standard LOOKUP function defaults to an approximate match and requires the lookup vector to be sorted in ascending order. If your data is unsorted, it will often return incorrect results or #N/A errors. Using VLOOKUP allows you to specify an exact match search, which is ideal for matching specific names.
Click on the cell containing your current LOOKUP formula that is returning the #N/A error.
Replace your existing formula with a VLOOKUP formula: `=VLOOKUP($FC$4,$R$4:$S$17,2,FALSE)`. The FALSE argument guarantees that the function will only return a result for an exact name match.
If you need to drag and fill the formula down multiple rows, adjust the absolute referencing to `=VLOOKUP($FC4,$R$4:$S$17,2,FALSE)` so the row number can change dynamically while keeping the table array locked.
Clean Data to Remove Hidden Spaces
Remove leading and trailing spaces that cause text mismatches in lookup formulas.
Fix #N/A Lookup Errors Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools, including VLOOKUP, XLOOKUP, and built-in data cleaning features to quickly resolve formula #N/A errors without hassle.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the file containing the lookup errors.
- 2. Check the error indicator: Select the cell with the #N/A error and click the Error Checking alert icon that appears next to it for context.
- 3. Apply the exact match formula: Replace the LOOKUP formula in the formula bar with `=VLOOKUP($FC$4,$R$4:$S$17,2,FALSE)` and press Enter.
- 4. Clean the source data: If the error persists, use the TRIM function or the 'Text to Columns' feature under the Data tab to eliminate hidden spaces causing the mismatch.
Frequently Asked Questions
Why does the LOOKUP function return #N/A even when the value exists?
The classic LOOKUP function expects the lookup vector to be sorted in ascending order. If it is not sorted, the formula might not find the correct value, resulting in an #N/A error. Furthermore, trailing spaces or mismatched data types can cause the lookup to fail entirely.
What is the difference between LOOKUP and VLOOKUP?
LOOKUP defaults to an approximate match and requires data to be sorted. VLOOKUP searches vertically down the first column of a table and allows you to specify whether you want an exact match (FALSE) or an approximate match (TRUE), making it much more reliable for exact text searches.
How do I fix #N/A errors if VLOOKUP still isn't working?
If VLOOKUP with the FALSE argument still returns #N/A, check for hidden spaces using the TRIM function, ensure the data types match perfectly (e.g., make sure numbers aren't formatted as text), and verify that your table array range is locked using absolute references (like $R$4:$S$17).
