Search code examples
excelif-statementexcel-formulalookupworksheet-function

Formula to generate a value according to a range

I want to populate a cell according to the range of another cell. Like if the independent cell has the value between 12 to 16 then the dependent cell will be populated as -2 and if the independent cell has a value between 16 to 20 then -1 and if 20-24 then N and so on:

Sample
Sample

Is there any formula or how otherwise can I do it?

Solution

  • Assuming 16 is "between" 16-20 rather than "between" 12-16 and that for N it is the text of the OP that counts rather than the image: 

    =LOOKUP(A1,{12,16,20,24},{-2,-1,"N"})

    Negative values, zero, numbers less than 12 and positive or greater than 24, blanks and text would return #N/A.

    LOOKUP.