This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Using spacer characters in helper columns

Using spacer characters in helper columns

Using spacer characters in helper columns

What are helper columns?

Using helper columns is a very common practice in Excel.  If you’re not familiar with the term, it is a column which stores intermediate results to be used in other calculations.  Look at the example below:

helper column example error

If we wanted to VLOOKUP on the Product or the Part Number we could just use a normal VLOOKUP, but what if we want to VLOOKUP on both columns at the same time?  This is where the helper column comes in handy.

In Column C, the values of Column A and Column B have been combined.  In Cell C2 the formula used to combine the columns is:

=A2&B2

The & symbol can be used to join any two numbers or text strings together, one after the other.  This formula has been copied down to all the other rows in the dataset.


Advertisement:

We can use the helper column with a simple VLOOKUP, rather than using other more complex formulas.  A formula based on a helper column will usually calculate much faster than using complex array formulas, so they are a good way to solve complex calculation issues without complex calculations.

 

When helper columns go wrong

Depending on the layout of your data it is possible for helper column based calculations to return incorrect values.  Let’s look at an example:

In cell G5 of the image below we could enter the following:

=VLOOKUP(G2&G3,C2:D11,2,FALSE)

This formula is combining the Product and Part Numbers together so that they match the format used in the helper column.  But this formula does not show the correct result.


Advertisement:

We are looking up Product 674 and Part Number G7854-8890, but the VLOOKUP is not returning the correct value of 28,969, instead, the incorrect value of 47,345 is returned.

The issue is that there are other combinations of Product and Part Number, which provide the same result.  Cell C4 has the same combined value as cell C9 – The Product and Part Numbers are different, but the combined value is the same.

 

Always user spacer characters

I recommend you use a ‘spacer character’ between columns being combined.  A ‘spacer character’ is just a single character which is used to denote the end of one column and the start of the next.  You will want to use a character which would not be found anywhere in the data being combined.  This will vary depending on the data set, but often the special characters such as ^ or ¬ are good, as they very rarely appear in source data.

Let’s update our example with a ‘spacer character’.


Advertisement:

Cell C2 has been updated with a ^ as the space character and has been copied down to the bottom of the list.

=A2&"^"&B2

Cell G5 has also been updated with a ^ spacer character.

=VLOOKUP(G2&"^"&G3,C2:D11,2,FALSE)

Now our VLOOKUP provides the correct result.

helper columns spacer character

 

Conclusion

When combining columns to be used as part of other calculations always combine with a ‘spacer character’.  If we do not use spacer characters we run the risk of returning an incorrect value.  The probability may be small, but why take the risk?