在函数中实现vlookup和match [英] Implementing vlookup and match in function

问题描述

我正在尝试在表格中创建一个功能，该功能结合了"Vlookup"和匹配"我经常使用的组合.

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

我想使用我的函数"Rates"接受1个参数并返回Vlookup和Match的组合，该组合始终使用相同的值.

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(参数，DEFINED RANGE(始终保持相同的定义范围)，match(A1(始终为A1单元格，DIFFERENT DEFINED RANGE，0)，FALSE)

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

我尝试创建脚本，但是没有编码经验，并且收到错误消息未定义vlookup".

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);
}

实际结果:#ERROR!

ReferenceError:"vlookup"；没有定义. (第2行).

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

推荐答案

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;
}

这篇关于在函数中实现vlookup和match的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！