Vlookup Result Plus Next ‘n’ Rows in Google Sheets

Published on

How to return the next row in Vlookup in Google Sheets. Returning the next column is very simple. Here is that awesome tips to return Vlookup result plus next ‘n’ rows.

In my Google Sheets Vlookup formula variations, I have detailed how to Vlookup to return next ‘n’ columns.

It’s all about adding multiple index column numbers in Vlookup as comma separated that within Curly Braces. Forgot to say then the formula must be entered as Array Formula.

Regarding Vlookup result plus next 1 row, 2 rows or ‘n’ rows, the things are quite different. Here in this Google Spreadsheet tutorial, I am detailing that.

How to Vlookup Result Plus Next 'n' Rows

Instead of providing you the formula first, I am going to help you develop the formula step-by-step. So that you will be able to modify the formula later.

How to Return Vlookup Result Plus Next ‘n’ Rows

Follow the below steps carefully. I have coded the formula in cell J2 in the above example. You can see how to code the formula below.

Vlookup Output Plus Next 0 Row (Step # 1)

The below Vlookup formula uses the search key in cell H2 and return the result from cell B4 as the search found the match in cell A4. Needless to mention the index column is 2.

Formula # 1

=vlookup(H2,A2:E6,2,0)

Return Cell Address of Vlookup Result (Step # 2)

To return the cell address of Vlookup result in Google Doc Sheets, use the below formula that uses the function Cell.

Formula # 2

=cell("address",vlookup(H2,A1:E6,2,0)

Keep this formula aside. We need this as it is later.

Return Vlookup Index Column Letter (Step # 3)

To return the Vlookup result column letter, use the Left function with the formula # 2 above.

Formula # 3

=left(cell("address",vlookup(H2,A1:E6,2,0)),3)

Similar to Formula # 2 keep this formula aside. We want this later to form a combo formula that can return Vlookup result Plus Next ‘n’ rows in Google Sheets.

Return Vlookup Result Row Number (Step # 4)

Other than the above formula 2 and 3 we need this formula too. To find the row number of a Vlookup output you can use the Right function with formula # 2 as below.

Formula # 4

=right(cell("address",vlookup(H2,A1:E6,2,0)),1)

Generic Formula to Return Vlookup Result Plus Next 1 Row

See the generic formula below in that the +1 at the last part represents next 1 row.

indirect(Formula # 2 &":"&Formula # 3 & Formula # 4+1)

See the screen capture above. You can see that cell I2 contains a number. You can replace the +1 in the above formula with the cell reference I2 that represents ‘n’.

Vlookup Result + Next ‘n’ Rows (Step # 5)

Final Formula based on the above Generic Formula.

=indirect(cell("address",vlookup(H2,A1:E6,2,0))&":"&left(cell("address",vlookup(H2,A1:E6,2,0)),3)&right(cell("address",vlookup(H2,A1:E6,2,0)),1)+I2)

Conclusion

I am not claiming that this is the one and only option to return Vlookup Result Plus Next ‘n’ Rows in Google Sheets. You can possibly make a different combination using Index and Match.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.