Using match to categorize

A common Excel task is to categorize values into bands. You can use Frequency or some pivot table capabilities to report on that, but lets say that you want to add a categorization characteristic to individual rows but want to avoid complex IF’s and array formulas. Here’s how

Let’s say that you have in A1 an age, and you want to categorize into 0-10,10-20, above 20. This formula will do it for you.

=IFERROR(CHOOSE(MATCH(TRUE,{10,20,10000}>a1,0)," 0-10","10-20"," over 20"),"-")

Walkthrough

      • Set up an Array constant with the upper values of each range {10,20,10000}
      • Use Match to find the first case where the upper value is > A1. MATCH(TRUE,{10,20,10000}>a1,0)
      • Use Choose with the index returned by Match to pick the appropriate Text representation of the category. CHOOSE(MATCH(TRUE,{10,20,10000}>a1,0),” 0-10″,”10-20″,” over 20″)
      • Deal with error case =IFERROR(CHOOSE(MATCH(TRUE,{10,20,10000}>a1,0),” 0-10″,”10-20″,” over 20″),”-“)

For more tips like this see Get Started Snippets

For help and more information join our community,  follow the blog or  follow me on Twitter