ColumnA ColumnB
jhinz 115
tom 116
The idea behind this code is someone enters a number (lets say 116), the computer looks it up in column B and returns the name in column A (tom)
The only part I need help on for the code is the computer looking up the value in column 116.
I was trying to do a for loop with a nested if statement but it wasn't working. Could someone help me?
Yes, it is possible to emulate many of the built-in functions by using Class Spreadsheet (SpreadsheetApp) and JavaScript Array Object and its methods, but "the emulations" usually will be slower than built-in functions.
To do a left Vlookup, use Google Sheets Index Match formula. Vlookup in Google Sheets is case-insensitive, meaning it does not distinguish lowercase and uppercase characters. For case-sensitive lookup, use this formula.
VLOOKUP or Vertical Lookup is a function in Google Sheets that allows you to search vertically from top to bottom for a value (search_key) in a table (data range) and return the value of a specified cell in the row of that value (search_key).
Google Apps Script is a coding language based on JavaScript that allows you to extend and manipulate Google apps like Drive, Sheets, Docs, and Gmail.
in its simplest form and to see the working principle you could try this :
function findinB() {
var sh = SpreadsheetApp.getActiveSheet();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var last=ss.getLastRow();
var data=sh.getRange(1,1,last,2).getValues();// create an array of data from columns A and B
var valB=Browser.inputBox('Enter value to search in B')
for(nn=0;nn<data.length;++nn){
if (data[nn][1]==valB){break} ;// if a match in column B is found, break the loop
}
Browser.msgBox(data[nn][0]);// show column A
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With