# MS Excel question - VLOOKUP returning #VALUE instead of #N/A

**URL:** <https://boards.straightdope.com/t/ms-excel-question-vlookup-returning-value-instead-of-n-a/530408>\
**Category:** Factual Questions\
**Created:** [February 25, 2010, 5:07pm UTC](https://boards.straightdope.com/t/ms-excel-question-vlookup-returning-value-instead-of-n-a/530408 "2010-02-25T17:07:02Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jadis](https://avatars.discourse-cdn.com/v4/letter/j/87869e/32.png) [@Jadis](https://boards.straightdope.com/u/Jadis)\
**Post date:** [February 25, 2010, 5:07pm UTC](https://boards.straightdope.com/t/ms-excel-question-vlookup-returning-value-instead-of-n-a/530408/1 "2010-02-25T17:07:02Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![nivlac](https://avatars.discourse-cdn.com/v4/letter/n/3bc359/32.png) [@nivlac](https://boards.straightdope.com/u/nivlac)\
**Post date:** [February 25, 2010, 5:15pm UTC](https://boards.straightdope.com/t/ms-excel-question-vlookup-returning-value-instead-of-n-a/530408/2 "2010-02-25T17:15:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Jadis](https://avatars.discourse-cdn.com/v4/letter/j/87869e/32.png) [@Jadis](https://boards.straightdope.com/u/Jadis)\
**Post date:** [February 25, 2010, 5:26pm UTC](https://boards.straightdope.com/t/ms-excel-question-vlookup-returning-value-instead-of-n-a/530408/3 "2010-02-25T17:26:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Jadis](https://avatars.discourse-cdn.com/v4/letter/j/87869e/32.png) [@Jadis](https://boards.straightdope.com/u/Jadis)\
**Post date:** [February 25, 2010, 6:09pm UTC](https://boards.straightdope.com/t/ms-excel-question-vlookup-returning-value-instead-of-n-a/530408/4 "2010-02-25T18:09:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Bone](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bone/32/407_2.png) [@Bone](https://boards.straightdope.com/u/Bone)\
**Post date:** [February 25, 2010, 7:57pm UTC](https://boards.straightdope.com/t/ms-excel-question-vlookup-returning-value-instead-of-n-a/530408/5 "2010-02-25T19:57:49Z")

</div>

Instead of the function “isna” you can use “iserror” and it should work for both cases.