Vlookup N/A Error

Jak3t

New Member
Joined
Apr 18, 2013
Messages
6
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?
 
Last edited:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Jak3t

New Member
Joined
Apr 18, 2013
Messages
6
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

Board Regular
Joined
Sep 23, 2009
Messages
195
Try

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

Best Regards
Kenn
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

New Member
Joined
Apr 18, 2013
Messages
6
I require the lookup to find an exact match, omitting the T/F parameter defaults to True.
 

Jak3t

New Member
Joined
Apr 18, 2013
Messages
6

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Apr 18, 2013
Messages
6
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top