How to Create a Virtual Helper Column in Google Sheets

0
144
How to Create a Virtual Helper Column in Google Sheets

In this tutorial I am trying to make you clear 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 is a helper column. So here we go.

What is Helper Column in Google Sheets?

Technically, there is no any Helper Column in Google Sheets. We can describe a helper column as below.

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

Example 1:

Google Sheets Helper Column

In this example Column C is a helper column. It’s actually not a part of the data set in A1:B8. What is the use of this Helper Column then?

When you sort  the above data set, select the data range as A1:C8 that including the helper column C. So that, in a future date, when you want the data in A1:B8 in its original order, you can sort it by column C.

Example 2:

Helper Column with Vlookup in Google Sheets

vlookup with helper column in google sheets

In this example, Column A is helper Column which I’ve used in the Vlookup formula to find the position of “Mack Jordan”. Since our original set of data in B1:D8 contain the names as First Name and Second Name, we can’t find the position of Mack Jordan using Vlookup. Because Vlookup looks for the match in the first column only. So without a helper column we can look for only “Mack” not “Mack Jordan”.

The Steps Followed in Generating Helper Column for Vlookup Above:

In Column A1 I’ve used the JOIN formula to join the texts in Column B and C as below. I’ve applied shortcut key Ctrl+~ then to just show you the formula in Column A.

Join formula in Helper Column

Hope you could understand the helper column concept above. Now we can see How to Create a Virtual Helper Column in Google Sheets.

Virtual Helper Column in Google Sheets

If you understand the use of helper column, then it’s easy to create a virtual helper column.

What is Virtual Helper Column?

Virtual helper column is a set of formula that can use as a helper column. We can create a virtual helper column in Google Sheets very easily from a helper column. In any blank cell you just need to join the formula in the helper column with “;” as delimiter and wrap it with Curly Brackets. I will explain it below.

How to Create a Virtual Helper Column?

See the example 2 above where I’ve used Column A as helper column. The same helper column we can convert as virtual helper column. But when you create a virtual helper column, first you should insert the helper column as the last column of your existing set of data, not column A. So here in our example, Column D is the helper column.

Here I’ve just joined the First Names and Last Names in Column A and Column B.

virtual helper column - step 1

How that formulas in Column D visible in the screenshot? When you use shortcut key Ctrl+~, the formula became visible.

Now remove the “=” sign in front of the JOIN formula from all the cells in Column D. Then again use the above shortcut key which is equal to the Menu View > Show formula.

In any blank cell, here for example purpose I’m using Cell F2, apply the below formula to join the Column D content.

=join(“;”,D2:D8)

Then right click on Cell F2, copy the content, again right click and choose Paste Special > Paste Values only. Finally insert open and close Curly Braces as below to make it an array.

{join(” “,A2,B2);join(” “,A3,B3);join(” “,A4,B4);join(” “,A5,B5);join(” “,A6,B6);join(” “,A7,B7);join(” “,A8,B8)}

This way we can create a Virtual Helper Column in Google Sheets. This long text is your virtual helper column. Now you can delete column D.

How to Use this Virtual Helper Column in Formula?

Our original set of data spread across the range A2:C8. Now you can make the above helper column in front of the range A2:C8 as below. It has a virtual effect of inserting a new column A to use as helper column similar to our Example 2 above.

{{join(” “,A2,B2);join(” “,A3,B3);join(” “,A4,B4);join(” “,A5,B5);join(” “,A6,B6);join(” “,A7,B7);join(” “,A8,B8)},{A2:C8}}

Now in Vlookup I am using this as the data range.

Vlookup with Virtual Helper Column

=vlookup(“Mack Jordan”,{{join(” “,A2,B2);join(” “,A3,B3);join(” “,A4,B4);join(” “,A5,B5);join(” “,A6,B6);join(” “,A7,B7);join(” “,A8,B8)},{A2:C8}},4)

In the example 2 above, I’ve used Helper Column with Vlookup. Here the same example but with a virtual helper column. So don’t forget to compare it with Example 2 above.

Vlookup with Virtual Helper Column

Pros and Cons of Virtual Helper Column?

A virtual column is ideal to use when the number of rows in the data set are limited. But a Helper column has no such limitations.

I’ve two awesome spreadsheet tutorials related to Virtual Helper Column. Here it is.

1. Google Sheets Query Hidden Row Handling with Virtual Helper Column

2. SUMIF Excluding Hidden Rows in Google Sheets

Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here