0

I have a value "venue", which I want to match with a unique value in a different sheet ("Options"), column J.

Much like the normal vlookup formula, I want my script to return the value from column K, from the same row where my value matched.

I don't need this all as a standalone function, I need this as part of a bigger script & also define the looked up value as a variable.

I'm a novice with appscripts and grateful for any help.

Here's what I tried:

// Lookup Template
let venue = "Test";
let options = ss.getSheetByName("Options")
let venuelist = options.getRange('J2:J100').getValues();
var template;
     for(nn = 0; nn < venuelist.length; ++nn) {
     if(venuelist[nn][1] == venue) {
         var template = venuelist[nn][0];
         break;
     }
 }

And it's not working.

LionelHutz
  • 13
  • 3

1 Answers1

0

Try this.

// Lookup Template
let venue = "Test";
let options = ss.getSheetByName("Options")
let venuelist = options.getRange('J2:K100').getValues();
var template;
     for(let nn = 0; nn < venuelist.length; nn++) {
     if(venuelist[nn][0] == venue) {
         template = venuelist[nn][1];
         break;
     }
 }

Assuming your rows will keep on increeasing and it's not a one time activity, you can use the range 'J2:K' instead of 'J2:K100' so that it will check till the last row which has data.

ab.it.gcp
  • 151
  • 1
  • 14
  • Thanks so much for your answer. For some reason it's not working yet. Are you sure your code's output for var template is the value from column K and not column J? Could you walk me through the for/if part? – LionelHutz Mar 08 '23 at 14:07
  • I am assuming that you are searching for the venue "Test" in column K and based on that row index, you want the corresponding value in column J to be set in template. Can you share if there is any specific error you are getting? – ab.it.gcp Mar 08 '23 at 14:10
  • Ah. Yeah it's supposed to be the other way around :-) Search for value in column J, return value from column K. – LionelHutz Mar 08 '23 at 14:12
  • Ok, updated the code accordingly! Please try now. – ab.it.gcp Mar 08 '23 at 14:13