View Full Version : MS Excel question - VLOOKUP returning #VALUE instead of #N/A

02-25-2010, 11:07 AM
I use the VLOOKUP function pretty frequently, and I've never had a problem with it before, but I'm trying to integrate some data population into a few spreadsheets that I've inherited at work, and for some reason in these spreadsheets, the VLOOKUP formula is returning #VALUE when it fails to find a match instead of #N/A as I'm accustomed.

This makes it impossible to use the ISNA function in an IF statement in order to set all non-matches to zero. Since the non-matches are coming up as #VALUE instead, I then have to go back and manually zero them all out, which is, as you can imagine, a giant PITA.

Anyone have thoughts on what could be wrong with my data that it's not returning a normal #N/A?

02-25-2010, 11:15 AM
The problem is related to the first parameter in the vlookup -- the lookup_value. So verify that this parameter's value is not longer than 255 characters or if there's something else anomalous about it.

02-25-2010, 11:26 AM
If the lookup_value were anomalous somehow, wouldn't I be getting no matches at all? The function is working when there is a match, I'm getting the expected data back, it's only when there's no match that I'm having the #VALUE issue.

02-25-2010, 12:09 PM
FWIW, I just found this MS Support document (http://support.microsoft.com/kb/87442) that refers to Transition Formula Evaluation causing this error. I tried the workaround suggested on that page (clearing the TFE checkbox under File/Options/Transitions) and it worked. My no-match formulas are now returning #N/A instead of #VALUE.

02-25-2010, 01:57 PM
Instead of the function "isna" you can use "iserror" and it should work for both cases.

Send questions for Cecil Adams to: cecil@straightdope.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright 2018 STM Reader, LLC.