Vlookup N/A Error

[Jak3t]

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?

 

[Aladin Akyurek]

Care to post the erring formula?

 

[Jak3t]

I fear it won't help you much but here it is none the less:
VLOOKUP(B82+BP152,SunAnglesData!$D$4:$D$32,1,FALSE)
B82 is a calculated angle using ATAN and SQRT functions,
BP152 is the result of an array function that finds the minimum difference between B82 and each of the range of values contained in D4:D32.
The Vlookup thus returns the value closest to B82 from the array D4:D32. (or should do if it didn't have the N/A error)

 

[Kenn]

Try

VLOOKUP(B82+BP152,SunAnglesData!$D$4:$D$32,1)

Best Regards
Kenn

 

[Aladin Akyurek]

I fear it won't help you much but here it is none the less:
VLOOKUP(B82+BP152,SunAnglesData!$D$4:$D$32,1,FALSE)
B82 is a calculated angle using ATAN and SQRT functions,
BP152 is the result of an array function that finds the minimum difference between B82 and each of the range of values contained in D4:D32.
The Vlookup thus returns the value closest to B82 from the array D4:D32. (or should do if it didn't have the N/A error)

Care also post the formula in BP152?

 

[Jak3t]

I require the lookup to find an exact match, omitting the T/F parameter defaults to True.

 

[Jak3t]

Formula in BP152
{=MIN(ABS(SunAnglesData!$D$4:$D$32-B82))}

 

[Aladin Akyurek]

I fear it won't help you much but here it is none the less:
VLOOKUP(B82+BP152,SunAnglesData!$D$4:$D$32,1,FALSE)
B82 is a calculated angle using ATAN and SQRT functions,
BP152 is the result of an array function that finds the minimum difference between B82 and each of the range of values contained in D4:D32.
The Vlookup thus returns the value closest to B82 from the array D4:D32. (or should do if it didn't have the N/A error)

I require the lookup to find an exact match, omitting the T/F parameter defaults to True.

Formula in BP152
{=MIN(ABS(SunAnglesData!$D$4:$D$32-B82))}

Is either...

=VLOOKUP(ROUND(B82+BP152,2),SunAnglesData!$D$4:$D$32,1,0)

or, control+shift+enter...

=VLOOKUP(ROUND(B82+BP152,2),ROUND(SunAnglesData!$D$4:$D$32,2),1,0)

is admissible?

 

[Jak3t]

Admissible in theory yes - however the result is even more N/A errors. I don't think it's rounding errors as the data is already rounded to 10 decimial places and as I say the lookup and array values are EXACTLY the same, so rounding them doesn't help.
Thanks for the idea though.

 

[Aladin Akyurek]

Admissible in theory yes - however the result is even more N/A errors. I don't think it's rounding errors as the data is already rounded to 10 decimial places and as I say the lookup and array values are EXACTLY the same, so rounding them doesn't help.
Thanks for the idea though.

One more diagnostic...

Control+shift+enter:

=VLOOKUP(B82+BP152,SunAnglesData!$D$4:$D$32+0,1,0)

If this does not resolve anything, try to provide a file, including only the VLOOKUP bit thru a site like dropbox.