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. 

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'Reilly, Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.


For help and more information join our community,  follow the blog,  follow me on twitter, or follow me on g+

Comments