Discussion Locked

Return 0 instead of N/A with Vlookup

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Does anyone know how I can change the return result to zero instead of #N/A when the result is false, I thought of using an IF statement but I cant seem to get the syntax right.

4 replies

by chuck_huffman

You can use the ISNA() function which will return a true if the value is "NA" to evaluate your vlookup in a if statment ie
=IF(ISNA(VLOOKUP(H3,I3:J9,1,FALSE)),0,VLOOKUP(H3,I3:J9,1,FALSE))

by lord foul

In your lookup table (table array) add another row at the top that accomodates the 0 value. remember to always sort the table from lowest value to highest, top/down.

you will then return a 0 value

:D

This discussion is closed to new replies.