How to return the next row in Vlookup in Google Sheets. Returning the next column is very simple. Here
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
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.