if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasifs

Formula to look in first column before heading

I am currently using a function in Column J as below =ArrayFormula(IFERROR(Vlookup(if(U3:U<>"",(if(U3:U<5,"Hot",if(U3:U<10,"Warm",if(U3:U<30,"Cold",if(U3:U>30,"Lost"))))),),CellRef!A1:B,2,0)))

Column U contains Numerical data which return the value accordingly via vlookup, But what if i want the function to look into Column L first and if data found return value, if nothing is found in Column L it should start looking into Column U.

p.s. Column L already contains text and not numbers

sheet link here https://docs.google.com/spreadsheets/d/1dXZlC4i_l1WGCp6tbiXUFPhYBr3oc7b0V0sUbzG313M/edit?usp=sharing

Solution

  • Use ifs(), like this:

    =arrayformula( 
  iferror( 
    vlookup( 
      ifs( 
        len(L3:L), L3:L, 
        V3:V = "", iferror(1/0), 
        V3:V < 5, "Hot", 
        V3:V < 11, "Warm", 
        V3:V < 30, "Cold", 
        V3:V >= 30, "Lost" 
      ), 
      CellRef!A1:B, 
      columns(CellRef!A1:B), 
      false 
    ) 
  ) 
)

    See your sample spreadsheet.