Search code examples
google-sheetsgoogle-sheets-formula

Trying to make a Google sheets formula to change the value of a cell based on the range of a value in another cell

I’m trying to change the value of column D starting with row 12 and ending at row 103 based on the corresponding C value next to it. I want the column D cell to say “Wine” if the column C cell has a value of 89 or higher. If it’s between 78 and 88 to say “Cinnamon”. If. It’s between 67 and 77 to say “Mustard”. If it’s between 66 and 76 to say “olive drab. And if its 65 or less to say chocolate brown.

(https://i.sstatic.net/zKgY0.png)

I tried using an array formula but I don't really understand functions so it didn’t work.

Solution

  • You may try this within Cell_D12 of your test sheet

    =let(Σ,{0  ,"brown";
        56 ,"olive";
        67 ,"mustard";
        78 ,"cinnamon";
        89 ,"wine"},
     index(if(len(C12:C103),ifna(vlookup(C12:C103,Σ,2,1)),)))