Implementing vlookup and match in function

Question

I'm trying to create a function in Sheets that combines a "Vlookup" and "Match" combination that I use frequently.

I want to use my function, "Rates" to accept 1 argument and return a combination of Vlookup and Match, that always uses the same values.

Vlookup(argument, DEFINED RANGE (always stays the same defined range), match(A1 (always cell A1), DIFFERENT DEFINED RANGE, 0), FALSE)

I have tried creating a script, but have no experience coding, and I receive an error that "vlookup is not defined"

function ratesearch(service) {
  return vlookup(service, Rates, Match($A$1,RatesIndex,0),FALSE);
}

Actual results: #ERROR!

ReferenceError: "vlookup" is not defined. (line 2).

Answer 1

function findRate() {
  var accountName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,1).getValue(); //determine the account name to use in the horizontal search
  var rateTab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Rates'); //hold the name of the rate tab for further dissection
  var rateNumColumns =rateTab.getLastColumn(); //count the number of columns on the rate tab so we can later create an array
  var rateNumRows = rateTab.getLastRow(); //count the number of rows on the rate tab so we can create an array
  var rateSheet = rateTab.getRange(1,1,rateNumRows,rateNumColumns).getValues(); //create an array based on the number of rows & columns on the rate tab
  var currentRow = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell().getRow(); //gets the current row so we can get the name of the rate to search
  var rateToSearch = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(currentRow,1).getValue(); //gets the name of the rate to search on the rates tab
  for(rr=0;rr<rateSheet.length;++rr){
    if (rateSheet[rr][0]==rateToSearch){break} ;// if we find the name of the 
      }
  for(cc=0;cc<rateNumColumns;++cc){
    if (rateSheet[0][cc]==accountName){break};
      }
  var rate = rateSheet[rr][cc] ; //the value of the rate as specified by rate name and account name
  return rate;
}

Answer 2

Optimization points for Alex's answer:

1. Never forget to declare variables with var, const or let (rr and cc). If you omit the keyword, the variables will be global and cause you a lot of trouble (as they will not reset after the loop finishes). The best way is to use block-scoped let.
2. Following #1, do not rely on out-of-scope variables (rateSheet[rr][cc]).
3. You do not need to call SpreadsheetApp.getActiveSpreadsheet() multiple times - that's what variables are for. Call once, then reuse.
4. getRange(1,1,<last row>, <last col>) is equivalent to a single getDataRange call.
5. use find or findIndex method to avoid verbose loops.

With the points applied, you get a clean and optimized function to use:

const findRate = () => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const accountName = ss.getActiveSheet().getRange(1, 1).getValue();

  const rateTab = ss.getSheetByName("Rates");

  const rates = rateTab.getDataRange().getValues();

  const currentRow = ss.getActiveSheet().getActiveCell().getRow();

  var rateToSearch = ss.getActiveSheet().getRange(currentRow, 1).getValue();

  const rr = rates.findIndex((rate) => rate === rateToSearch);

  const [firstRates] = rates;

  const cc = firstRates.findIndex((rate) => rate === accountName);

  return rates[rr][cc];
};

---

Note that the "vlookup" is not defined error indicates there is no vlookup variable / function declaration in scope. Which obviously is the case as there is no built-in Google Apps Script vlookup function.

Answer 3

You can't access random ranges from a custom function so you would have to provide the data to the function, some of the other solutions here that use get active spreadsheet won't work as a custom function which I am guessing is what the OP is looking for, here is an example of a script that does that but word of warning before you go down this road, custom functions are much slower than the built in functions so doing this will be much slower than vlookup and match, if you only have a few functions like this in the sheet you will be fine, but if you build large tables with dozens of rows that use custom functions it will slow down you spreadsheet substantially.

// Combines VLOOKUP and MATCH into a single function
// equivalent to VLOOKUP(rowValue, tableData, MATCH(columnName, tableHeader))
// but in this version tableData includes tableHeader
function findInTable(tableData, columnName, rowValue) {
  if (rowValue === "") {
    return "";
  }
  if (tableData.length == 0) {
    return "Empty Table";
  }
  const header = tableData[0];
  const index = header.indexOf(columnName);
  if (index == -1) {
    return `Can't find columnName: ${columnName}`;
  }

  const row = tableData.find(row => row[0] == rowValue);
  if (row === undefined) {
    return `Can't find row for rowValue: ${rowValue}`;
  }
  return row[index];
}

Another optimization I suggest you do is use named ranges, it allows you to transform something like: =VLOOKUP(C5, 'Other Sheet'!A2:G782, MATCH("Column Name", 'Other Sheet'!A1:G1)) into a more readable and easier to look at: =VLOOKUP(C5, someTableData, MATCH("Column Name", someTableHeader))

for the custom function form this will look like: =findInTable(A1:G782, "Column Name", C5) Note that I shorted the argument list by merging the data and header, this makes some assumptions about the table structure, e.g. that there is a one header line and that the lookup value is in the first column but it makes it even shorter and easier to read. But as mention before this comes at the cost of being slower. I ended up giving up on using this for my needs due to how slow it is and how much faster VLOOKUP and MATCH are since they are built in functions.

Answer 4

vlookup is not something you can use in a function in a script, it is a spreadsheet formula.

Google Scripts use JavaScript, so you'll need to write your code in JS then output it to a relevant cell.

If you could share your sheet/script we could help figure it out with you.