Search code examples
google-sheetscomparearray-formulas

Google Sheets - Comparing a column against a table to retrieve a tag

If I have a column of values A{2,11,101,1001} and I have a table with two columns C/D{Tag1/1,Tag2/10,Tag3/100....}

I want to have an array formula that compares the values from A and sees if where they fit in the range... if less than 100 but more than 10 column B should show Tag2 etc...

Solution 
  • =IFERROR(ARRAYFORMULA(VLOOKUP(A1:A,
 {QUERY(IFERROR(ARRAYFORMULA(VALUE(
  REGEXEXTRACT(D1:D&"-"&C1:C, "(\d+)"))), ), "SELECT Col1"),
  REGEXEXTRACT(QUERY(D1:D&"-"&C1:C, "SELECT Col1"),"-(.+)")}, 2)), )

    9