Here’s a sample OfficeScript that demonstrates how to add a VLOOKUP formula to a cell in Excel:
function addVLookupFormula() { // Get the active worksheet var worksheet = context.workbook.getActiveWorksheet(); // Define the cell where you want to add the VLOOKUP formula var cell = worksheet.getRange("A1"); // Define the lookup value and range for the VLOOKUP formula var lookupValue = "John"; var lookupRange = worksheet.getRange("D2:E6"); // Create the VLOOKUP formula var vlookupFormula = "=VLOOKUP(" + lookupValue + "," + lookupRange.getAddress() + ",2,FALSE)"; // Set the formula to the cell cell.formulas = [[vlookupFormula]]; // Calculate the formula worksheet.calculate(); }
In this example, the addVLookupFormula function performs the following steps:
It gets the active worksheet using the
getActiveWorksheetmethod.It defines the cell where you want to add the VLOOKUP formula using the
getRangemethod. Adjust the range as needed.It defines the lookup value and range for the VLOOKUP formula. In this example, the lookup value is “John” and the lookup range is “D2:E6”. Adjust the values and range as needed.
It creates the VLOOKUP formula by concatenating the lookup value, lookup range address, and other parameters.
It sets the formula to the cell using the
formulasproperty of the cell.It calculates the formula using the
calculatemethod on the worksheet.
You can call the addVLookupFormula function to add a VLOOKUP formula to the specified cell in Excel. The formula will perform a vertical lookup based on the lookup value and range, and the result will be displayed in the cell.
Please note that this example assumes you have an open workbook in Excel for the web or Excel Online, where you can execute the OfficeScript.
Additionally, remember to set up the necessary context and load the OfficeScript runtime to ensure the script executes successfully.