logo
down
shadow

Adding Multiple Column to Google Script Vlookup function


Adding Multiple Column to Google Script Vlookup function

By : user2950702
Date : November 17 2020, 11:58 AM
To fix the issue you can do @AD:AM I thought that *sign(Row(A2:A)) was required when used inside of an arrayformula for a multi-Column Call. But I just tried it with a brand new sheet and it looks like you're right! And i just tried it out but new sheets still doesn't support Vlookup Multi-Column Calls outside of arrays.
See: https://github.com/preactive/GAS-Helper-Function/blob/master/Lookup_.js & https://docs.google.com/a/costco.com/spreadsheets/d/1nDOFWeXfeFEGvCjhH0YHunjDuh4atCVMpiEiRV7wAFM/edit#gid=0
code :
function FinderLookUpReturnArrayRange_(Search_Key,SearchKey_Ref_IndexOffSet,Ref_Sheet,Ref_Range,IndexOffSetForReturn,Set_Sheet,Set_PosRow,Set_PosCol)   
{
var twoDimensionalArray = [];
var data = SpreadsheetApp.getActive().getSheetByName(Ref_Sheet).getRange(Ref_Range).getValues();         //Syncs sheet by name and range into var
for (var i = 0; i<Search_Key.length; i++)  // i = number of rows to index and search  
{
var Sending = [];
var newArray = [];
for (nn=0;nn<data.length;nn++)  // nn = number of row data is found at
{
  if (data[nn][SearchKey_Ref_IndexOffSet]==Search_Key[i]) //if statement is triggered when the search_key is found.
  {
    for (cc=0;cc<IndexOffSetForReturn.length;cc++) //cc = numbers of columns to ref
    {
      var iosr = IndexOffSetForReturn[cc];
      var Sending = data[nn][iosr];
      if(isEmpty_(Sending)==true) //if statement for if one of the returned cells is blank
      {
      var Sending =  "#N/A";
      }
      if (IndexOffSetForReturn.length>1) //if statement for multi-Column returns
      {
        newArray.push(Sending);
        if(IndexOffSetForReturn.length-1 == cc) // if statement for pulling all columns into larger array
        {
          twoDimensionalArray.push(newArray);  
          break;
        }
      }
      else if (IndexOffSetForReturn.length<=1) //if statement for single-Column returns
      {
        twoDimensionalArray.push(Sending);
        break;
      }
    }
  }
  if(data.length-1==nn && isEmpty_(Sending)==true) //following if statement is for if the current item in lookup array is not found.  Nessessary for data structure.
  {
    for(na=0;na<IndexOffSetForReturn.length;na++) //looping for the number of columns to place "#N/A" in to preserve data structure
    {
      var Sending = "#N/A";
      newArray.push(Sending);
    }
    twoDimensionalArray.push(newArray);  
  }
}
}
if(typeof Set_PosRow != "number")
{
var Set_PosRow = getFirstEmptyRowUsingArray_(Set_Sheet);    //for usage in a database like entry without having to manually look for the next level.
}
for (var l = 0; l < Search_Key.length; l++)                                    //Builds 2d Looping-Array to allow choosing of columns at a future point
{
if (IndexOffSetForReturn.length<=1)  //checks to see if it's a single column return
{
  SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_PosRow + l,Set_PosCol).setValue(twoDimensionalArray[l]);
}
}
if (IndexOffSetForReturn.length>1)  //checks to see if it's a multi column return
{
  SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_PosRow,Set_PosCol,twoDimensionalArray.length,twoDimensionalArray[0].length).setValues(twoDimensionalArray);
}
}
function isEmpty_(string) 
{

if(!string)             return true;         
if(string == '')        return true;
if(string === false)    return true; 
if(string === null)     return true; 
if(string == undefined) return true;
string = string+' '; // check for a bunch of whitespace
if('' == (string.replace(/^\s\s*/, '').replace(/\s\s*$/, ''))) return true;       
return false;        
}
function getFirstEmptyRowUsingArray_(sheetname) 
{
  var data = SpreadsheetApp.getActive().getSheetByName(sheetname).getDataRange().getValues();
  for(var n = data.length ; n<0 ;  n--)
  {
    if(isEmpty_(data[n][0])=false)
    {
      n++;
      break;
    }
  }
  n++
    return (n);
}


Share : facebook icon twitter icon
Script in Google Spreadsheets - VLOOKUP

Script in Google Spreadsheets - VLOOKUP


By : Chris Corr
Date : March 29 2020, 07:55 AM
wish helps you What error is being generated?
The best would be to use the method setFormula(formula):
code :
...
sheet2.getRange(c, 1).setFormula(vlookupCell);
...
VLookup adding another column to the search

VLookup adding another column to the search


By : user2102680
Date : March 29 2020, 07:55 AM
VLookup with multiple results adding values

VLookup with multiple results adding values


By : Mehdi Saeedifar
Date : March 29 2020, 07:55 AM
wish of those help I would like to lookup a value in another excel file that will have multiple results as rows. For example If the specific value is found in the column A of that row I want to add the number in column c to a total and then output that. , As @BigBen suggested in the comment, use =SUMIF() formula.
Vlookup or Index and return multiple adjacent rows in next column in Google Sheets

Vlookup or Index and return multiple adjacent rows in next column in Google Sheets


By : user3446845
Date : March 29 2020, 07:55 AM
I hope this helps you . I'm not sure this is possible. I have column A which I'm searching for text, then I want to return column B + the 5 adjacent rows below and as separate cells, not a sum. Some of those may be blank. Here's an example of what I'm working with. The position of the number in column B can change, however, I will always want a fixed amount of rows. I have tried using the index but I was hoping for 1 formula to return multiple rows. Here's what I have =INDEX(B21:B190,MATCH($L$1,A21:A190,0)+1) (then +2, +3, +4, +5 etc...: , try:
code :
=ARRAYFORMULA(IFNA(QUERY({VLOOKUP(ROW(A3:A), 
 IF(A3:A<>"", {ROW(A3:A), A3:A}), 2, 1), B3:B}, 
 "select Col2 where Col1 = '"&D3&"'", 0)))
Google Script version of VLookup (More Efficient Method?)

Google Script version of VLookup (More Efficient Method?)


By : user3693582
Date : March 29 2020, 07:55 AM
it helps some times I'm trying to put together a function that will allow me to pull a column's info from one sheet to another based on a key column. This would work similar to an index match or vlookup in excel/google. , Issue:
Slow script:
code :
const ss = SpreadsheetApp.getActive();
/**
 * @param {GoogleAppsScript.Spreadsheet.Sheet} fromSht -Sheet to import from
 * @param {GoogleAppsScript.Spreadsheet.Sheet} toSht -Sheet to import to
 * @param {Number} fromCompCol -Column number of fromSht to compare
 * @param {Number} toCompCol -Column number of toSht to compare
 * @param {Number} fromCol -Column number of fromSht to get result
 * @param {Number} toCol -Column number of toSht to get result
 */
function vlookup_2(
  fromSht = ss.getSheetByName('Sheet1'),
  toSht = ss.getSheetByName('Sheet2'),
  fromCompCol = 1,
  toCompCol = 1,
  fromCol = 2,
  toCol = 2
) {
  const toShtLr = toSht.getLastRow();
  const toCompArr = toSht.getRange(2, toCompCol, toShtLr - 1, 1).getValues();
  const fromArr = fromSht.getDataRange().getValues();
  fromCompCol--;
  fromCol--;

  /*Create a hash object of fromSheet*/
  const obj1 = fromArr.reduce((obj, row) => {
    let el = row[fromCompCol];
    el in obj ? null : (obj[el] = row[fromCol]);
    return obj;
  }, {});

  //Paste to column
  toSht
    .getRange(2, toCol, toShtLr - 1, 1)
    .setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [null])));
}

Related Posts Related Posts :
  • javascript - explanation request of a behaviour
  • Converting String into date format in JS
  • Choose element Angular
  • Display background grid by using image with CSS
  • Call function from another Controller in another file AngularJS
  • How to change texture in Gera library?
  • When trying to use windows.location.href , the values of $_POST[''] is lost
  • jQuery click action on appended item
  • NodeJS & Socket.IO: Emit a request event and get the response, when/where should I bind the listener?
  • 4-digit form on bootstrap modal
  • Prevent characters like quotation marks from input
  • Sign in option creation using jsp and oracle database
  • use socket.io in my controller with yeoman
  • What happens under the hood to prevent calling a bound method changing the this object?
  • issue with jquery .append function
  • Is it possible to EventHandle every GET request a page makes via Javascript?
  • How to select class with ng-if
  • Cannot communicate with websocket. Autobahn: received HELLO message, and session is not yet established
  • Socket.io - Cannot connect to client
  • Load Accordian Panel on click rather than page load
  • How to register async middleware module within express.js app?
  • JS / Cannot read property 'push' of undefined
  • how to make jquery plugin?
  • MATH reverting rotated coordinates
  • Add an expandable text box to every post
  • What was the rationale behind having the receiver in functions default to the global object?
  • JavaScript chaining many function
  • while loop over takes the each method
  • What are the difference between $(document).bind('ready', function) and $(document).ready(function() {})
  • cut the string in javascript
  • Tool tip not working on text area bootstrap
  • AngularJS: how to activate first non zero item in ngRepeat?
  • Pattern for "column total" of an array of arrays
  • Any selector using * failed in my case
  • Uncaught ReferenceError: app is not defined in Angularjs
  • Unit testing controller which has a $state.go method in controller
  • Use of Meteor-ui-progress-circle (accessing to Template variables created in the HTML)
  • JavaScript: Google maps mapOptions?
  • How do I track Universal Analytics events through Google Tag Manager?
  • Firefox Scrolling Bug When Changing :after Contents
  • Drop down list does not work
  • Display month and year specific datepicker in jquery
  • How to show BAr graph rather than line graph in High Stoch Chart
  • Accessing Angular $Scope in web workers
  • How to convert JavaScript(UnityScript) to C# and implement a low pass filter?
  • Need a logic to implement select button value via onchange event
  • redirect in codeigniter using ajax function
  • How to populate this form data and post it with jquery
  • Retrive value from array id in js function
  • angularjs $index when ng-hide with ng-repeat
  • ExtJS TaskRunner
  • Progress bar not hiding after fetching values via AJAX in JavaScript
  • Pop up window code fails
  • how to make dynamic javascript array with key and value group
  • Implement Facebook API login with reactjs
  • What is the meaning of sign ':' in javascript
  • Change all id's in HTMLDivElement using jQuery
  • load multiple pages from different domain in different divs using javascript
  • Could Not Resolve Host github.com
  • jQuery "Chosen" on-filter event?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org