Is there an option or setting that prevents VLOOKUP from returning zero when it finds blank cells

Question

Is there an option or setting that prevents VLOOKUP from returning zero when it finds blank cells or does not find the lookup value.

I do not want to suppress the zeros by formating them out. I want VLOOKUP to return the cell as it is or show NA if it can't find the value.

Answer 1

Not that I know of, but there are some work arounds.

I usually just bring in an IF statement:

=IF(VLOOKUP(*blah, blah, blah*)=0,"NA",VLOOKUP(*blah, blah, blah*))

And will often error trap it with:

=IFERROR(IF(VLOOKUP(*blah, blah, blah*)=0,"NA",VLOOKUP(*blah, blah, blah*)),"NA")

Also, sometimes I will use conditional formatting for all cells = 0, show with white font etc.

And you can even try a customn number format like:

0;(0);""

Answer 2

If you have the latest version of Excel, try using XLOOKUP instead.

To get rid of the zero, you can concatenate an empty string to the return_array.

For example:

=XLOOKUP(lookup_value,lookup_array,return_array&"","NA")

Concatenating the empty string to the return_array will of course cause all return values from that formula to be formatted as text. So, apply an appropriate wrapper function to re-convert to number formats:

=VALUE(XLOOKUP(lookup_value,lookup_array,return_array&"","NA"))

Answer 3

Select the cell where you want to store the formula, and click the "Insert Function" button in turn to open the "Insert Function" option.

In the "Insert Function" dialog box, click the "Select Category" drop-down button and select "Logic" in the drop-down menu; select the "IF" function in the "Select Function" list box, click the "OK" button, and the "Function" will pop up Parameters" dialog box.

In the "Logical-test" parameter text box, enter your vlookup formula, and then you can select the content returned when true or false