I think I have broken Excel. I have a Vlookup function returning a N/A error completely inexplicably.
The vlookup lookup value exists in the array (formatted exactly the same, a numerical value identical to the lookup value verified by the EXACT function etc) but the result is a N/A error.
The odd thing in in a table of over 900 identical formulas only a dozen or so return the N/A error. Both the lookup value and the array values are the results of many formulas which rely on lookups, averages, additions/subtractions and tan/cos/sin/sqrt functions. The data tables are huge but everything worked until I doubled the source data. All formulas remained the same (excepting the references to the expanded data) but now I get just a few errors.
I have tried re-writing the Vlookup as an Index/Match formula with the same results. Both the vlookup and match functions are looking for exact values and the array data contains the appropriate values which I have checked and double checked are identical to the lookup value, formatted identically and are numerical with no additional spaces etc.
I have even tried copying the lookup values and the array data and pasting as values in a new sheet and I still get the error. Even more bizzarly though if I double click on the lookup value and press enter it fixes the N/A error. Initially I thought this meant that the lookup value was being stored as text however I have gone through all the data in my original sheet and multiplied each cell by 1 to ensure it is being stored as a number to no avail.
What could possibly be the problem?
The vlookup lookup value exists in the array (formatted exactly the same, a numerical value identical to the lookup value verified by the EXACT function etc) but the result is a N/A error.
The odd thing in in a table of over 900 identical formulas only a dozen or so return the N/A error. Both the lookup value and the array values are the results of many formulas which rely on lookups, averages, additions/subtractions and tan/cos/sin/sqrt functions. The data tables are huge but everything worked until I doubled the source data. All formulas remained the same (excepting the references to the expanded data) but now I get just a few errors.
I have tried re-writing the Vlookup as an Index/Match formula with the same results. Both the vlookup and match functions are looking for exact values and the array data contains the appropriate values which I have checked and double checked are identical to the lookup value, formatted identically and are numerical with no additional spaces etc.
I have even tried copying the lookup values and the array data and pasting as values in a new sheet and I still get the error. Even more bizzarly though if I double click on the lookup value and press enter it fixes the N/A error. Initially I thought this meant that the lookup value was being stored as text however I have gone through all the data in my original sheet and multiplied each cell by 1 to ensure it is being stored as a number to no avail.
What could possibly be the problem?
Last edited:
