Combine in App Script Vlookup with IF Statement

Question

Thanks in advance for taking the time to review this thread. I am trying to recreate a formula in App Script that combines Vlookup and multiple IF Statements. Having reviewed just about every post I could find I was able to create the vlookup in app script but the script only works if the lookup value matches the exact value in the other sheet which is not always true. As such I am not sure whether the script should be adjusted to have multiple IF statements similar to the formula it is replacing or whether there is a better option.

Here is an example workbook that has the arrayformula and script. https://docs.google.com/spreadsheets/d/1N2GJR-Otl0Idf2_wTx9XsdFsxj4SDw329uKRM10ivkE/edit?usp=sharing

My main sheet contains a list of product codes(Skus) that are used to look up the product cost in another workbook. Depending on the product, the Product Code(Sku) in the main sheet may contain additional characters which will result in the lookup value not being an exact match with the Product Code in the other workbook. As shown in the picture below, in the products sheet the Product Code (SKU) has 3 variations:

1. Regular - If the product code does NOT start with “S” or contain “@” then it will match the Product Code in the other workbook.
2. Vintage - Some products have the vintage added to Sku. For example Sku 18 might be entered as Sku 18@2021. In order for the lookup value to work the @2021 needs to be removed.
3. S Product - If the Sku starts with an “S” it means that the product is being split into smaller units. In order to lookup the product cost the leading “S” needs to be removed but the cost value also needs to be divided by the number of units.

[![enter image description here][1]][1]

=ARRAYFORMULA(IFERROR(IF(ROW(A1:A)=1,"Cost",

IF((REGEXMATCH(A1:A,"@")+(LEFT(A1:A,1)="S"))=0,
VLOOKUP(A1:A,{Imp_Epl!B:B,Imp_Epl!F:F},2,FALSE),

IF(LEFT(A1:A,1)="S",
VLOOKUP(RIGHT(A1:A, LEN(A1:A)-1),{Imp_Epl!B:B,Imp_Epl!F:F},2,FALSE)/VLOOKUP(RIGHT(A1:A, LEN(A1:A)-1),{Imp_Epl!B:B,Imp_Epl!D:D},2,FALSE),

IF(REGEXMATCH(A1:A, "@"),
VLOOKUP( LEFT(A1:A, SEARCH("@",A1:A) -1) ,{Imp_Epl!B:B,Imp_Epl!F:F},2,FALSE))
))))))

Here is the Vlookup script

function vlookupalternative() {
const dstwb = SpreadsheetApp.getActiveSpreadsheet();              // Destination workbook
const srcwb = SpreadsheetApp.openById("152Rexxxxxxxxxx");         // Source workbook Id
const dstsheet = dstwb.getSheetByName("COST_SHEET");       // Destination workbook sheet name
const srcsheet = srcwb.getSheetByName("PRODUCTS_SHEET");    // Source workbook sheet name

const srcdata = srcsheet.getRange(2,2,srcsheet.getLastRow()-1,5).getValues()
const searchValues = dstsheet.getRange(2,1,dstsheet.getLastRow()-1,1).getValues()  

const dstheader = dstsheet.getRange(1,1,1,dstsheet.getLastColumn()).getValues()[0].indexOf("PutDatahere")+1; 

const matchSku = searchValues.map(searchRow => {
const matchRow = srcdata.find(r => r[0] == searchRow[0])
return matchRow ? [matchRow[4]] : [null]      // Columns to the right to retrieve value
})

dstsheet.getRange(2,dstheader,dstsheet.getLastRow()-1,1).setValues(matchSku)   
}

Answer 1

Although I'm not sure whether I could correctly understand your expected result, from your replying, does the following sample script obtain your expected result?

Sample script:

function sample() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const productList = ss.getSheetByName("Product List");
  const costSheet = ss.getSheetByName("Cost Sheet");
  const range = productList.getRange("A2:A" + productList.getLastRow());
  const costObj = costSheet.getRange("A2:I" + costSheet.getLastRow()).getValues().reduce((o, [, b, , d, , , , , i]) => (o[b] = [d, i], o), {});
  const values = range.getDisplayValues().map(([a]) => {
    const [t1, t2] = a.split("@")[0].split("S");
    return [t2 ? (costObj[t2] ? (costObj[t2][1] / costObj[t2][0]) : null) : (costObj[t1] ? costObj[t1][1] : null)];
  });
  range.offset(0, 11).setValues(values);
}

Testing:

When this script is used in your provided Spreadsheet, the following result is obtained.

Note:

• This sample script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

• If you want to reduce the process cost more, how about retrieving the values from "Cost Sheet" with Sheets API as follows? In this case, please enable Sheets API at Advanced Google services.

  function sample2() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const productList = ss.getSheetByName("Product List");
    const range = productList.getRange("A2:A" + productList.getLastRow());
    const costObj = Sheets.Spreadsheets.Values.get(ss.getId(), "'Cost Sheet'!A2:I", { valueRenderOption: "UNFORMATTED_VALUE" }).values.reduce((o, [, b, , d, , , , , i]) => (o[b] = [d, i], o), {});
    const values = range.getDisplayValues().map(([a]) => {
      const [t1, t2] = a.split("@")[0].split("S");
      return [t2 ? (costObj[t2] ? (costObj[t2][1] / costObj[t2][0]) : null) : (costObj[t1] ? costObj[t1][1] : null)];
    });
    range.offset(0, 11).setValues(values);
  }
  

References:

• reduce()
• map()

Answer 2

The problem is related to the exact match feature from VLOOKUP, in this scenario to avoid using too many arguments, the solution I would like to share is using IFERROR, instead of an IF, IFERROR Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.

The solution is to use REGEXREPLACE

in order to remove the "S" and the "@2023" from the SKU and replace it with a blank, this is the regular_expression:

[A-Z](?:)|@[0-9]*

Based on the first Google Sheet example you shared, I copied the information on a new Google Sheet using the same exact cell range. Here's the formula:

=IFERROR(VLOOKUP(A4, Sheet1!$B$13:$E$17, 4, FALSE), VLOOKUP(VALUE(REGEXREPLACE(A4, "[A-Z](?:)|@[0-9]*", "")), Sheet1!B11:E15, 4, FALSE))

Reference:

• IFERROR
• REGEXREPLACE