Why does this newly copied and pasted dataset fail?

Why did it fail? Let’s see what the reason behind the failure is along with a more robust solution. You copy and paste new data into your worksheet and apply the same function’s logic…and it fails. One of Excel’s versatile text manipulation functions, the TRIM function, can assist with the lookup function to “see” the data without the presence of the extra leading/training spaces. It is these leading or trailing spaces that Excel sees quite easily that our eyes don’t notice without special investigative steps. The number being searched appears to be in the table, yet the lookup function can’t locate it and returns an error.Īlthough this error can be caused by many factors, it is often attributed to extra spaces before and/or after one of the values ( the value being searched for or the corresponding value in the table.) When dealing with “other people’s” data, it’s not uncommon for users to create what appears to be an easy, straight-forward formula that should have no problem working, but instead fails in the most baffling way.Ī typical example is where a user writes something like a lookup function to find a part number in a table to return a description.

Non breaking space code 160