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

• 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

• 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 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.

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’.

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.

Learn More About Lookup Formulas and Binning

• True Vlookup Tragedy post by Kevin Lehrbass
• How to Use VLOOKUP to Find the Closest Match (TRUE)  post by Jon Acampora
• VLOOKUP Example Explained at Starbucks post by Jon Acampora
• Pricing Tier Lookup Using Sumifs instead of lookups  post by Chandoo
• 12 Different Binning Solutions & Ideas  post by Kevin Lehrbass
• Vlookup versus Index Match video by Kevin Lehrbass

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.

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.