I spent a long time using the for loops in the other answers to emulate vlookup. It works wonderfully if you don't have much data....but the more data you have, the longer it takes, which can get quite frustrating! Here is the solution I found which avoids the for loop:
var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = SpreadsheetApp.openById("ID").getSheetByName("Sheet1").getRange('A2:Z').getValues()
var searchValue = s.getRange("A2").getValue();
var dataList = data.map(x => x[0])
var index = dataList.indexOf(searchValue);
if (index === -1) {
throw new Error('Value not found')
} else {
var foundValue = data[index][1]
s.getRange("B2").setValue(foundValue);
}
It's a pretty short code, so now I'll go through it line by line for you. This is how I did it as part of a larger function, but you could easily turn it into a function that returns, by feeding in some of the data I grab here.
var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = SpreadsheetApp.openById("ID").getSheetByName("Sheet1").getRange('A2:Z').getValues();
Here, I grab the sheets that I wish to use. For s, this is the page I'm performing the 'vlookup' on. You can also use getSheetByName() here if you prefer to call an individual sheet. For data, that's the search range we want to use. For the data variable, you can grab the ID of the sheet by grabbing the sheet code in the url between /d/ and /edit. For getSheetByName, that's the name of the sheet. You can then
var searchValue = s.getRange("A2").getValue();
Here, I use getRange() and getValue()/to grab first the value I wish to search for (change A2 to grab the cell of the value you want to search for).
var dataList = data.map(x => x[0])
Here, I create an array of ONLY the first column of my data. This is what I will be matching on!
var index = dataList.indexOf(searchValue);
Here, i use indexOf, which is a tool you can use on arrays to get the position within the array. So, for example, if something is the 5th item in your array, it will return 6 (because arrays start the count with 0). This essentially gets what "row" it is in my data.
if (index === -1) {
throw new Error('Value not found')
} else {
If the value is not found within the list, index will return -1. This first portion of the if block covers what to do if the value is not found, which you can customize for your needs.
var foundValue = data[index][1]
Then, we can use the index item we got to grab the row in the data we want. Then we just have to say which column we want with the second call. If you are looking for the second column (Column B), you would put [1].
s.getRange("B2").setValue(foundValue);
Finally, I set the value where I want my 'new' value to go to the value I found! You could also instead use it as a variable in whatever you are trying to do.
If you want to find multiple matches, use lastIndexOf() and a while loop. As long as index is not -1, I grab the lastIndexOf that item, and then splice it. If you want to emulate arrayformula(vlookup(, you could also do the original item, but with a for loop going through the different 'search' values.
I know this is a very late answer, but all of my scripts sped up dramatically when I discovered it, so I wished to share :) Hope it helps!
SpreadsheetApp
) and JavaScript Array Object and its methods.