In this tutorial, I am trying to make you understand the virtual helper column concept in Google Sheets and its pros and cons.

Let’s see how to create a virtual helper column in Google Sheets. Before that, you should know what a helper column is. So here we go.

What is a Helper Column in Google Sheets?

We can describe it as below.

A helper column is an additional column in a data set to use as a supporting column to simplify formulas.

Here are two examples.

1. With Sorting

In this example, column C is a helper column. It’s not a part of the data set in A1:B8.

What is the use of this helper column, then?

When you sort the above dataset, select the data range A1:C8, which includes the helper column C.

So that, on a future date, when you want the data in A1:B8 in its original order, you can sort it by column C in ascending order.

2. With Vlookup

In this example, column A is a helper column that I’ve used in the VLOOKUP formula to find the “Position” of “Mack Jordan.”

Since our data in B1:D8 contains the names as First Name and Last Name, we can’t find the “Position” of “Mack Jordan” using Vlookup in the usual way.

Because, usually, Vlookup looks for the match in the first column (column B) only.

Here I intend to make you understand the virtual helper column concept. 

So, follow this example to understand what is a virtual helper column and its use in Vlookup.

Important: A virtual helper column is beneficial in handling hidden rows in Google Sheets. I’ve added those tips at the end of this post.

Here is the formula in cell A2 that I used for generating the helper column for the Vlookup above.

=ArrayFormula(B2:B8&" "&C2:C8)

I hope you could well understand the helper column concept above.

Now we can see how to create a virtual helper column in Google Sheets.

What is a Virtual Helper Column in Google Sheets?

If you understand a helper column, it’s easy to create a virtual helper column.

A virtual helper column is an array formula that replaces a physical helper column.

See above example 2, where I’ve used column A (A2:A8) as a physical helper column.

How to create a virtual helper column in Google Sheets?

It entirely depends on your requirement. If we consider the above Vlookup, we can use it as below.

Add the helper formula within the range part of Vlookup.

Original Range: B2:D8

Range with Virtual Helper Column: {virtual_helper_column_formula,B2:D8}

Formula:

=vlookup("Mack Jordan",{ArrayFormula(B2:B8&" "&C2:C8),B2:D8},4,0)

Pros and Cons of a Virtual Helper Column?

Physical Helper Column:

1. Pros: A helper column can make formulas simple to read.
2. Cons: You may require to hide the helper columns when you print data.

Virtual Helper Column:

1. Cons: A virtual column might makes the formula complex.
2. Pros: It avoids possible data clutter.

Do we require a helper or virtual helper column in formulas in Google Sheets?

Yes! It’s a must in scenarios where you want to omit hidden rows in formulas.

Please see the example under “BYROW Function to Include Visible Rows (Exclude Hidden Rows) in Aggregation” in my BYROW function guide.