Xlookup, If, etc formulas and the dreaded #Value! that comes with "" when pasting as values

[dogchow101]

In xlookup you can define what your error response is ~~ =xlookup(a3,b:b,c:c,<whateveryouwantyourerrorresponsetobe>,option,option). Typically I use "" as my error response. However when you copy the cell and paste as value (the result being an error state equaling "") in another cell location, or even another workbook, and try to do math on that pasted data, you always get a #Value! error. There should be an alternative option for the xlookup error response element that allows excel to see NOTHING. However that isn't the case and everyone wants the world to redo miles and miles of spreadsheets to function properly and output zeros and use conditional formatting. That doesn't work for everyone and also they don't necessarily want to see a zero in the cell data or under filter, they want an empty box ( "" does not equal 0 and in some cases under usage for accounting etc this is actually VERY IMPORTANT!!! ). So I found the fastest workaround to get through the quandry of having to execute the F2 function through even thousands of those lovely #Value! entries.

Just FYI This most likely won't work in contiguous row entries (only in contiguous column entries) all that well, but it works in columns lovely.

You can filter the column of #Value! entries down so that only those entries show. Swipe highlight, or Ctrl + down arrow (which should stop at the end of the data set). Here's the tricky part, down try to double click into the top cell in the cell stack, actually click in the formula edit box, only once, now you are in edit mode for the top most selected cell. At this point, and here's the loveliest part of this all, DON'T JUST HIT ENTER, use Ctrl + Enter, the Ctrl applies the action to all the selected cells and since you are in a filter, excel ignores all of the filtered away cells.

Voila!! all of the #Value! entries are gone!!!

Thank you very very much, I love you all, I'll be here all week!!

 

[Joe4]

@Fluff the destination cell is VALUES only. The value output of "" is not empty. I need the value output to be empty.

Then you cannot use formulas.
You might need to look into using a VBA solution instead.