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?
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.
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.
FWIW, I just found this MS Support document 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.