The trick is to build a custom table that will map values to all the groups you need. In the example shown, the formula in F5 is: VLOOKUP simply looks up the value and returns the group name from the 2nd column in the table. Both columns in the table can contain any values that you need, and the table doesn’t need to be sorted since VLOOKUP is using exact match. As always, if VLOOKUP can’t find a match, it will return #N/A.

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.