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, by department, as shown in this table? Well, you could use nested IF statements as we’ve covered in another video, and 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 to first build the helper table. With VLOOKUP, we need Department to be in the left-most column, and we need the lookup value to be on the right. Then we need a complete list of departments. This is a great-use case for Excel’s built-in “remove duplicates” feature. Just copy the values to another worksheet, and use Remove Duplicates on the Data tab of the ribbon. When I click OK, I get a full set of unique values, and I can just copy these back into the table. Now we need to add a group for each department. Finally, let’s use a named range for the table to make the VLOOKUP formula easier to read and copy. Next, I’ll enter the VLOOKUP formula we need to do the grouping. For the lookup value, we need Department, and for the look_up table I’ll use the named range group_map. Column index is 2 and 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 change 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 the values as you like. The VLOOKUP formula will continue to work normally.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
