Are you looking to add some dynamism to your Vlookup formulas in Google Sheets? Then the COLUMNS function might just be what you need! By replacing the “index” argument in Vlookup with the COLUMNS function, you can have more control over your formulas. But don’t worry, I’ll guide you through it step by step in this Google Sheets tutorial.
Sheets Vlookup without Columns Function
Let’s start with a normal Vlookup formula without the use of the COLUMNS function. For example, consider the following formula:
=VLOOKUP("Flashback Arrester",A2:E6,5,FALSE)This formula searches for the key “Flashback Arrester” in the first column of the range A2:E6 and returns the corresponding value from column 5. In this case, the result would be $120 from Cell E3.
Vlookup with Columns Function in Google Sheets
Now, let’s see how we can use the COLUMNS function to replace the “index” number in the Vlookup formula. Here’s an example:
=VLOOKUP("Flashback Arrester",A2:E6,COLUMNS(A2:E6),FALSE)In this formula, the COLUMNS function returns the number 5, which is used as the index number in the Vlookup. So both the formulas without the COLUMNS function and with the COLUMNS function return the same result.
Advantages of the Use of COLUMNS Function in Vlookup
The use of the COLUMNS function brings some dynamism to your Vlookup formulas in Google Sheets. Let’s explore some advantages of using the COLUMNS function:
Dynamic Vlookup Index Column
When you use the COLUMNS function in Vlookup, inserting new columns in your table won’t affect the result of your Vlookup formula. For example, if you insert a new column between columns D and E, the formulas will still give you the correct result without any changes.
Replacing Index in Vlookup with COLUMN Function
The COLUMN function can be used to replace multiple index numbers in an array in the Vlookup formula. This allows you to retrieve multiple column values as a result. For example:
=ArrayFormula(VLOOKUP("Pug Cutting Machine",A2:E6,column(A9:E9),FALSE))By using the COLUMN function instead of the COLUMNS function, you can have more flexibility in specifying the index numbers in the array.
And that’s it! These are the uses of the COLUMNS function in Vlookup in Google Sheets. I hope this tutorial has helped you add some dynamism to your Vlookup formulas. If you want to learn more about Google Sheets and other useful tips, visit Crawlan.com for more exciting content.
Happy Vlookup-ing!
