0

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

Skilz Work
  • 15
  • 4
  • you could create a new lookup array formed by the cartesian product of your current arrays and work with that. – z'' Dec 16 '22 at 20:33
  • share a copy / sample of your sheet with an example of the desired output – player0 Dec 16 '22 at 20:34
  • https://docs.google.com/spreadsheets/d/1dXZlC4i_l1WGCp6tbiXUFPhYBr3oc7b0V0sUbzG313M/edit?usp=sharing sheet link here – Skilz Work Dec 16 '22 at 20:57

1 Answers1

0

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.

doubleunary
  • 13,842
  • 3
  • 18
  • 51