# Create a group column based on existing values

Let’s say you have a dataset containing information about a car fleet and you need to create a column with usage group based on number of KM each car was used.

I’ll use this file, sheet [2 Removing duplicates] in the following examples.

### Step1: Calculate the sum per car

A simple sumif will give you the full distance.

1. =SUMIF(A:A,A2,D:D)

### Step2: Create groups

#### A. Using IF

Let’s say you need the following groups:
<=200 KM
201-300 KM
301-400 KM
>400 KM

1. =IF(F2<=200,"<=200 KM",IF(AND(F2>=201,F2<=300),"201-300 KM",IF(AND(F2>=301,F2<=400),"301-400 KM",IF(F2>400,">400 KM",0))))

#### B. Using VLOOKUP

Using IF might give you headaches so you could use VLOOKUP instead.

You need to create a lookup table like this:

 0 <=200 KM 200 201-300 KM 300 301-400 KM 400 >400 KM

Now, assuming you have the sum of KM in column F and the lookup table in columns I:J, you can use this formula:

1. =VLOOKUP(F2,I:J,2)

Note that range_lookup argument is missing so it will be an approximate search, VLOOKUP returning the next lowest match to your search term.

This site uses Akismet to reduce spam. Learn how your comment data is processed.