Search code examples
excelcategorization

Categorize data in Excel

I have sheet "data" with values like these:

9000
9100
9105
9200

I have another sheet "categories":

Low    High   Value
9000   9100   1
9101   9200   2
9201   9300   3

I want to obtain sheet "data in categories":

1
1
2
2

How can I accomplish this using formulas?

Solution

  • In A1 in your new sheet ('Data in Categories') try to use this formula

    =OFFSET(Categories!$C$1, SUMPRODUCT(--(Categories!$A$2:$A$4<=Data!A1)*(Categories!$B$2:$B$4>=Data!A1),ROW(Categories!$C$2:$C$4))-1, 0)

    and copy it down

    Result

    --------
|  A1  |
--------
|    1 |
|    1 |
|    2 |
|    2 |

    Source of the base idea of using SUMPRODUCT and detailed explanation is Range Lookup in Excel