Exceljet

Quick, clean, and to the point

How to group values with VLOOKUP

In this video, we'll look at how to use VLOOKUP to group data into specific categories.

Let's take a look.

Sometimes, you need to group values into discreet categories that don't exist in your data. For example, in this case we have a list of employees and each employee is in one department. What if you need to group these employees into groups A and B, according to department, as shown in this table?

Well, you could use you could use nested IF statements and, as we covered in another video. This works fine.

But another way to perform this same grouping is to use VLOOKUP with a helper table to map each department into the group you want.

To do this, we need first to build the helper table. With VLOOKUP, we need Department to be in the left-most column, and we need the lookup value to be to the right.

Then we need to complete list of departments. This is a great use case for Excel's built-in remove duplicates feature.

Just copy the values to a scratch sheet, and use Remove Duplicates on the Data tab of the ribbon. When I click OK, I get a full set of unique values, and can copy these back to the table.

Now we need to add the right group for each department. Finally, let's use a named range for the the table to make the VLOOKUP formula easier to read and copy.

Now I'll enter the VLOOKUP formula itself.

For the lookup value, we need Department, and for the look_up table I need to use the the group_map table. Column index is 2 and the match type is 0 (or false), to force an exact match.

When I copy this formula down, we get a group for each department.

What's nice about this approach, is that you can easily change the grouping just by changing the table.

You can also easily adjust the table to handle a new department.

Finally, Because VLOOKUP is set to exact match, lookup values do not need to be sorted -- you're free to arrange lookup values as you like.

Course 
Core Formula

Related shortcuts

Author 
Dave Bruns