Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Writing google Javascript similar to vlookup

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?

like image 1000
user1293629 Avatar asked May 31 '12 17:05

user1293629


People also ask

Is there a way to emulate VLOOKUP in Google script?

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.

What is the equivalent of VLOOKUP in Google Sheets?

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.

Can Google sheets do VLOOKUP?

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).

Is Appscript same as JavaScript?

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.


1 Answers

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
}
like image 53
Serge insas Avatar answered Oct 13 '22 06:10

Serge insas