12 Dec 2015

In Excel we can bin numbers. We can also bin letters. Rare but possible. However, be careful when binning a mix of letters and symbols! What could go wrong? A student who earns a B- could end up with an A- grade!

 

Review of MATCH and INDEX Functions

Back in January I created a video (included below) to explain various VLOOKUP binning dangers. This post uses Excel’s INDEX and MATCH functions which together do everything a VLOOKUP can do and more.

 

Excel’s MATCH function has three arguments

MATCH Function 12-5-2015 10-59-37 PM

  • lookup_value (What are we looking for?)
  • lookup_array (Where are we looking)
  • [match_type] (Exact match = 0 or false, closest match = 1 or true. Optional yet essential.)

 

Excel’s INDEX function has three arguments

INDEX 12-5-2015 11-19-04 PM

  • array (Where are our potential answers?)
  • row_num (Which one do we want?)
  • [column_num] (Which one do we want? This is optional. We don’t need it in this case as we have a vertical range only)

 

 

BINNING NUMBERS

This works. Using TRUE allows Excel to bin our number into our Bin Range (bin values must be sorted smallest to largest). 329 is greater than 300 but doesn’t reach 400. Our answer is ‘Group 4’.

Danger:

  • Bin Range numbers must be sorted correctly!
  • Each number should fit within one of the bin pairs.

_Binning Numbers_

 

 

BINNING LETTERS

This also works. Using TRUE allows Excel to bin our letter into our Bin Range only because the letters are in alphabetical order and are the same case (all upper case). I used CODE function to show each letter’s internal code number. 

So, binning letters of the same case (all upper or all lower) that are sorted properly is really like binning numbers. Here, our lookup letter N (code 78) is greater than K (code 75) but doesn’t reach P (code 80). Our answer is letter K and the INDEX function returns the answer of ‘Group 3’.

Danger:

  • It’s much safer to list all possible letters and use FALSE (exact match). This way you never have to be concerned about the letter sort order
  • Letters must be either all upper case or all lower case
  • Bin Range letters must be sorted correctly! Each number should fit within one of the bin pairs.

_Binning Letters_

 

 

NEVER BIN LETTERS and SYMBOLS!

Never bin a combination of letters and symbols with TRUE (or 1) as match type.

Even if your data is sorted it won’t consistently give you the correct answers. It will only consistently work if you use FALSE (exact match) not TRUE. Why?

We can’t mix letters and symbols (i.e. + -) when binning using a closest match (TRUE). It doesn’t work for all lookup values. In this case, a student with a B- would end up getting a grade of  A- !

However, since we have listed all of our possible lookup values in the lookup range we can and should use FALSE (exact match). Our answer should be ‘Group 6’ and not ‘Group 3’.

_Never Bin a Mix of Letters and Symbols_

 

 

BINARY SEARCH EXPLANATION BY BILL JELEN

Do you really want to know what happens with the binary closest match? Watch Bill Jelen’s video!

 

 

Why Would Anyone Bin Letters and Symbols?

I think the error happens something like this.

Someone, possibly a teacher, creates a binning table for grades with A, B, C, D, E, F.

With just letters, we can get the correct answer using closet match =MATCH(A32,$F$26:$F$40,TRUE) (as long as the letters are in the correct order) although it’s much safer to use exact match like this =MATCH(A32,$F$26:$F$40,FALSE)

As either formula works, some will use the closet match =MATCH(A32,$F$26:$F$40,TRUE) and then add additional grades:  A+, A-, B+, B-, etc.

This is when the problem starts. Some of the lookup values will still work and minimal testing may not discover the problem. So, a student could get a B- yet be awarded an A- value!

 

 

Download my Excel files

Download the file I used in the screen pics here or via my OneDrive (file 00115b)

Download the file I used in my previous video here or via my OneDrive (file 00115). It used VLOOKUP instead of INDEX MATCH but it’s the same idea.

 

 

Watch My Video

I explain the details of binning using VLOOKUP function instead of INDEX MATCH. It’s the same issue.

 

 

Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table

Here is an interesting vlookup by Tom Urtis. Tom has built the look array inside of the formula using an array constant. You can read Tom’s explanation here. The important thing to remember is that Tom is binning the numbers and the answers are the letter grades so this is perfectly fine.

Tom Uritis Lookup without a table12-7-2015 8-51-26 PM

 

Learn More About Lookup Formulas and Binning

 

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.

There are so many amazing things that you can do with this powerful software. Check out my videos and my blog posts.

Away from Excel I enjoy playing with my dogs Cali & Fenton, learning Spanish and playing Chess.