Daljit Dhadwal

Having leading or trailing spaces around text is one of the most common reasons why vlookup fails. The solution to this problem is simple: the trim function. Trim removes spaces from around text. Wrapping the lookup_value and the table_array inside the trim function makes vlookup much more sturdy. Trim operates on one value at a time. Since the table_array has many different values, you’ll need to enter the trimmed vlookup function as an array formula. If the data is extra messy, you’ll also need to wrap the lookup_value and the table_array with the clean function. In a few cases, you’ll also need to use the substitute function in combination with trim and clean.