0

I created this function to get the range from sheets dynamically

function GetSheetRange(j) {
return SpreadsheetApp.getActiveSpreadsheet().getSheets()[j].getRange(3,1,1000,7).getValues()
}

I wanted to use this function in a cell formula for vlookup

VLOOKUP(A28&" "&C28, GetSheetRange(6), 7, false)

But this formula is not working, the reason why I'm trying to do this is because sheet names can be edited without affecting the formula

Can someone please advise me how to use the range function inside vlookup?

1 Answer 1

0

You need to make changes to both the Apps script as well as the VLOOKUP() function.

THE SCRIPT

The second parameter in VLOOKUP() is a range. So GetSheetRange(j) needs to return the range address, including the Sheet Name.

Use this script:

/**
 * Returns a range in a sheet
 * @param {number} j Sheet number (zero based)
 * @return The range in the sheet number
 * @customfunction
 */
function GetSheetRange(j) {
SpreadsheetApp.flush()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var range = sheets[j].getRange(3,1,1000,7).getA1Notation() // Row 3, Column A, 1000 rows, 7 columns = A3:G1002
var sheetname = sheets[j].getSheetName()
var sheetrange = "'"+sheetname+"'!"+range
return sheetrange
}

VLOOKUP Range

The script returns a string. So one should use INDIRECT() to convert the string to the actual range values.

Use this formula: =VLOOKUP(A28&" "&C28, INDIRECT(GetSheetRange(6)), 7, false)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.